Update IMPORTRANGE function from list of URLs

Question

I have a spreadsheet that lists several URLs of other spreadsheets, all containing client data. This list is constantly being added to as new client sheets are created. I need to be able to aggregate all the client sheet data into one sheet automatically.

I have tried including the importrange text (without making it a formula) for each spreadsheet URL, and then doing ={'URL List'!A2:A}, but all that does is bring in the literal text of the formula.

I'm sure I need to use Google App Script to make this happen, possibly with a loop, but I can't figure out how to make it work.

What do you have so far? It is possible if: 1.) sheet are is same folder 2.) Sheetname is the same or there is always one sheet / first sheet 3.) range is always the same. — RemcoE33

RemcoE33 RemcoE33 · Accepted Answer · 2021-04-24T19:12:24

This is working:

Tools -> Script editor
Clear the little myFunction code you see and paste the code from below
Change some id's/sheetnames/ranges
Save and close
Tools -> Macro's -> Import -> Select: 'setImportRange'
Tools -> Macro's -> Manage -> Set shortcut

Now whenever you run via the menu -> Macro's or via the shortcut the formula will be updatet with all the sheets in that drive folder.

EDIT: If your sheetname has a space then you must surround with single quotes: "Data!A2:B" ---> "'Data sheet'!A2:B"

Code:

function setImportRange(){
  // Change id of drive folder
  const spreadsheets = DriveApp.getFolderById('1AxxxxxxxxxwxzZv825s-4m8LcnM3u3hm').getFiles();
  const importranges = [];

  while (spreadsheets.hasNext()){
    const ss = spreadsheets.next();
    //Change sheetname and range
    importranges.push(`IMPORTRANGE("${ss.getId()}", "Data!A2:B")`)
  }

//Change master sheetname and the cell where you want the formula.
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Master').getRange('A1').setFormula(`=QUERY({${importranges.join(';')}},"SELECT * WHERE Col1 IS NOT NULL",0)`)

}

EDIT: from list:

function setImportRangeFromList(){
  const sss = SpreadsheetApp.getActiveSpreadsheet();
  const inputSheet = sss.getSheetByName('List');
  // 2 = start row | 1 = Start column
  const spreadsheets = [].concat(...inputSheet.getRange(2,1,inputSheet.getLastRow()).getValues());
  const importranges = [];

  spreadsheets.forEach(ss => {
    //Change sheetname and range
    importranges.push(`IMPORTRANGE("${ss}", "Data!A2:B")`)
  });

  sss.getSheetByName('Master').getRange('A1').setFormula(`=QUERY({${importranges.join(';')}},"SELECT * WHERE Col1 IS NOT N",0)`)

}

Update IMPORTRANGE function from list of URLs

1 Answers