Connect two Google Sheets with script

Question

I want to create a sheet by SpreadsheetApp.create(); and to fill a QUERY in this new generated sheet via cell.setFormula and IMPORTRANGE to get data of another Sheet.

It doesn't work because the sheets are not connected. If I do so manually the QUERY works.

Is there a way to connect two sheets via script?

Tulio Troche Tulio Troche · Accepted Answer · 2016-09-26T14:24:24

You could create a variable with the locations of the sheets by the ID's. Then, access the sheet remotely via the script. Save the cell variable and paste this over into the first sheet.

Psuedo-Code Example

Sheet1 = SpreadsheetApp.getSheetByID('IDofSheetOne');
SpreadsheetApp.create();
Sheet2 = SpreadsheetApp.getSheetByID('IDofSheetTwo'); // the one just created, you'd probably have to use some kind of get to get this ID.

Now from here, you can just say, Sheet1.doWhatever() and Sheet2.doWhatever().

Thats how you can access more than one sheet at a time.

Connect two Google Sheets with script

1 Answers