Summarize data from multiple Google Spreadsheets into a single one.

[Damian Drewulski]

Hey!

Let's say I have a "master google spreadsheet" and multiple spreadsheets with the same structure (google form results). The first column of each of those spreadsheets is a timestamp. In the "master spreadsheet" I would like to have: 1) counted non-empty cells in the first column; 2) the latest timestamp (latest = the bottom one).

And here are the questions:

1. Is there a way I can get information from those multiple spreadsheets?
2. How to get "the last non-empty" cell of the column?

Best!

[Laurie Nason]

Hi Damian,

My solution would not be to use a script at all, this can be done easily using the standard functions in the master sheet

Depending on how many "multiple" spreadsheets you have (there's a maximum number of linked sheets - want to say around 200, but could be wrong there), I would probably do the following.

In the Master sheet - have one tab with a row for each "sub sheet" 

1. First column would be the URL of the sub sheet
2. Second column would have the name of the tab in the subsheet with the timestamp column assuming ColA (exclude header row)- e.g. ="'Form Responses'!A2:A"
3. Third Column, Row 2 for example would have a function like 
• "=INDEX(QUERY(IMPORTRANGE(A2,B2),"SELECT COUNT(Col1),MAX(Col1) WHERE Col1 is not null LABEL COUNT(Col1) '',MAX(Col1) ' ',0),2)
  
4. The first time you use this you'll have to enable the importrange to access the remote sheet - but hover and click allow
5. Explanation of formula - working from inside out:

• ImportRange - goes and gets the information in the timestamp column of the referenced sheet
• Query - uses this information to count the number of records returned that are not blank in the first column, and the maximum value of the data in the column in the second
• LABEL is required because Query has the annoying habit of adding in a second header row for max and count columns - this sets the value to a blank space (not strictly necessary here because of...
• INDEX - removes that extra header row by only retrieving the second row

I really hope that this makes sense to you - I think it should pretty much work for you - and it's dynamic, will update when the underlying data updates (you can change the frequency of checking I believe by changing the master sheet properties)

Laurie

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/47f07a9a-3657-43b0-ad22-647520e415ccn%40googlegroups.com.

[Damian Drewulski]

Laurie!

it does exactly what I wanted <3 

(I needed to chop it into pieces to understand + there was missing double quote) but you helped a lot. Thank you very much!

Best!

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/NgZLsbiE1Gw/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CA%2BA7ZWKAF1oZP%3DH4JwvVw6hTxaG0djwbU2MMFts2YrRhpzCrww%40mail.gmail.com.

--

Damian  Drewulski

Production Coordinator

Stara Drukarnia

Gdańska 130, 90-520 Łódź, Poland

+48 794 086 540

www.unit9.com

[Laurie Nason]

Great, happy to help. 

Obviously I left the double quotes out as a test ... ahem.... ;-)

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAE1YOEFDJ_Wf%2BNBEGAOe-H3fxAjFYh-XHwgRNv7bwG7Y_LkVvQ%40mail.gmail.com.