How to access multiple spreadsheets (not sheets) in one google app script?

Question

Background

I have 2 separate spreadsheets. They get google form submissions for window cleaning estimate requests from different mediums of communication;

one spreadsheet captures all emailed requests,
the another spreadsheet captures all requests by phone.

Whether by email or phone calls, the data that I capture is uniformly the same.

Consolidating responses

I found how to get edit updates from form submissions. But when the update occurs I want to move that data to another spreadsheet called 'the compiled list of estimates', which is exactly as it sounds. It compiles the data onto one spreadsheet once a form is submitted on any other spreadsheet I include in the code.

My Problem

How do I call on the other separate spreadsheets from my bound spreadsheet or would I have to write the code on the submission form spreadsheets and route that data to the compiler list?

I'm fairly new and just need some keywords and I'll be off to the races. You know how it is: 'you don't know what you don't know' - that's me with the syntax for this code.

May I clarify, please correct me if I'm wrong. You have two spreadsheets getting form responses; you want the responses from those spreadsheets to be moved/copied to a consolidated spreadsheet. Have you thought about putting the form response sheets in the same spreadsheet? That way you might use a query to summarise all the data onto a third "consolidated" sheet in the same spreadsheet. — Tedinoz
You can simply use the openById function -> developers.google.com/apps-script/reference/spreadsheet/… to get the Spreadsheet object to work with. Does that help you? — Andres Duarte
Tedinoz that's a great way to make things simpler I didn't know you could do that, how would I distinguish which responses came from which corresponding forms? — Zachary Henderson

MrPea MrPea · Accepted Answer · 2019-10-02T20:54:42

Some keywords/concepts you could look into:

so on your bound spreadsheet you can access the data from that spreadsheet using

var ss = SpreadsheetApp.getActiveSpreadsheet()

but within that bound spreadsheet in order to access a secondary spreadsheet you use

var ss_other = SpreadsheetApp.openById("xxxxxxx")

once you have the variable ss_other, any methods applied to it will be effected in the secondary spreadsheet

so for example to move the values in the range A1:B10 from one spreadsheet to another, you might do the following

var ss = SpreadsheetApp.getActiveSpreadsheet()
var sht = ss.getSheetByName("sourceSheet") //whatever the sheet is called with the source data
var rng = sht.getRange("A1:B10")
var values = rng.getValues()
var height = values.length
var width = values[0].length
var ss_dest = SpreadsheetApp.openById("xxxxxxx")
var sht_dest = ss_dest.getSheetByName("destSheet")
var destRng = sht_dest.getRange(1,1,height,width)
destRng.setValues(values)

where xxxxxx is the id of the spreadsheet which you can get from the URL when it is open in Sheets e.g https://docs.google.com/spreadsheets/d/xxxxxxx/edit

When using setValues() the destination range that you 'get' has to be the same size and shape as the source range i.e same number of rows and columns. You can't get the values from A1:B10 then just say setValues() to range 'A1' and expect it populate from A1 through to B10. Therefore if the source range is of variable size you have establish the size of the data in the source and use those dimensions in the destination getRange() - this is why the code above uses the variables 'height' and 'width' and the destination getRange() call uses relative references.

I hope the gives you something to go on,

How to access multiple spreadsheets (not sheets) in one google app script?

1 Answers