cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Using collections to populate a spreadsheet

Hi all

 

I'm looking at the Template canvas app for Employee Survey. It fits my needs almost perfectly but I am confident I can amend it myself.

 

What I am not sure of is what happens to the submitted data? It seems that on completion of the final question it populates a collection. How can I gather the submitted data into a spreadsheet?

 

I'm using the Employee survey app and have changed nothing of note apart from branding and the text of the questions.

 

Can you help?

7 REPLIES 7
Highlighted
Super User
Super User

Re: Using collections to populate a spreadsheet

Hi @Petecmsmith 

Put the following in the OnSelect property of a button.

Collect(Spreadsheetname, Collectionname).  The column names in the collection must match exactly the column names in the Spreadsheet.  If they don't, you can use the RenameColumns() command on the collection to make them match. They don't have to be in the same order as long as the collection is identical to the spreadsheet and all of the columns are accounted for.  NB. The Collect() command will not prevent duplicates from being submitted to the spreadsheet. 

Highlighted
Helper I
Helper I

Re: Using collections to populate a spreadsheet

Thank you, do you mean (tablename, collectionname) ?

And should this be the spreadsheet and table that I have just added in the data source?

 

I cannot actually find the data source for the questions that already exist within the template app. DId I wrongly think this was a ready made and working app with data source that I could just manipulate?

Highlighted
Super User
Super User

Re: Using collections to populate a spreadsheet

It should be where ever the data is stored.  You can find it here either under view > datasources or by clicking the can symbol on the left side. 

Annotation 2020-04-17 085406.png

Highlighted
Helper I
Helper I

Re: Using collections to populate a spreadsheet

Ok so it seems to be populating the spreadsheet!

 

But, I have a second collection that contains some more data that I need to get into my spreadsheet. My 2 collections are ResponseCollect and ListCollect. I think I have all the columns in my 2 tables and with the right formatting settings (text or number). My question is how to update both tables with the OnSelect button property? Or indeed can I update just one table if I put the additional columns into the one table?

 

Sorry for all the questions!

Highlighted
Super User
Super User

Re: Using collections to populate a spreadsheet

@Petecmsmith 

You can update both collections by putting two Collect functions separated by ";" in the OnSelect property of the button.

Collect(Table1, collection);Collect(Table2, collection)

However, if you storing the same information in two tables, it is likely that you have not designed your App properly.  Please see my series about how to properly design your database. https://powerusers.microsoft.com/t5/News-Announcements/Database-Design-Fundamentals-and-PowerApps-An... 

Highlighted
Helper I
Helper I

Re: Using collections to populate a spreadsheet

Thank you, I think the structure is ok as I am using the same structure as in the template app.

 

That said I'm getting errors on my second collect statement, my tables are called BedesResponse and BedesList and my formula is:

Collect(BedesResponse, Responsecollect); Collect(BedesList, Listcollect)

Responsecollect and List collect being my collections.

The error it is reporting is against the second collection statement "the function collect has some invalid arguments".

Highlighted
Super User
Super User

Re: Using collections to populate a spreadsheet

@Petecmsmith 

Make sure that the the spelling and case is correct and that the column names in the target table match the names in the collection.

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (8,151)