Importrange from multiple google spread sheets

Question

I know how to import data from ONE spread sheet to another:

=IMPORTRANGE(spreadsheet_key, range_string)

Source: https://support.google.com/docs/answer/3093340?hl=en&ref_topic=3105411

But how do I import MULTIPLE spread sheets into one "master spread sheet"?

Something like this:

=IMPORTRANGE(spreadsheet_key, range_string),IMPORTRANGE(spreadsheet_key, range_string),IMPORTRANGE(spreadsheet_key, range_string)

All spread sheets have the exact same structure.

I want all data in the same sheet in the same columns.

Preferebly I would like to import all spread sheets automaticly that are placed in one folder into the "master spread sheat".

Robin Gertenbach Robin Gertenbach · Accepted Answer · 2016-08-12T21:27:42

Formula

You can concatenate values and ranges.
In the English locale you concatenate columns with , and rows with ; annd putting them in curlies { and }.

Try the following:

={IMPORTRANGE(spreadsheet_key1, range_string1);
  IMPORTRANGE(spreadsheet_key2, range_string2);
  IMPORTRANGE(spreadsheet_key3, range_string3)}

Function

A rough method to automatically import all sheets in a folder.

function import() {
  var spreadsheets = DriveApp.
    getFolderById("YOURFOLDERID").
    getFilesByType(MimeType.GOOGLE_SHEETS);

  var data = [];
  while (spreadsheets.hasNext()) {
    var currentSpreadsheet = SpreadsheetApp.openById(spreadsheets.next().getId());
    data = data.concat(currentSpreadsheet.   
                         getSheetByName("YOURSTANDARDSHEETNAME").
                         getRange("YOURSTANDARDDATARANGE").
                         getValues());
  }

  SpreadsheetApp.
    getActiveSheet().
    getRange(1, 1, data.length, data[0].length).
    setValues(data);
}

This doesn't handle ranges that get smaller and thus not overwrite the old data or anything fancy.
It also still needs to be manually triggered.

Importrange from multiple google spread sheets

1 Answers

Formula

Function