CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Zachary Henderson
Zachary Henderson

Reputation: 33

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

Background

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

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.

Upvotes: 2

Views: 2638

Answers (1)

MrPea
MrPea

Reputation: 655

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,

Upvotes: 4

Related Questions