CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
TaylerQ
TaylerQ

Reputation: 15

Update IMPORTRANGE function from list of URLs

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.

Upvotes: 0

Views: 1398

Answers (1)

RemcoE33
RemcoE33

Reputation: 1610

This is working:

  1. Tools -> Script editor
  2. Clear the little myFunction code you see and paste the code from below
  3. Change some id's/sheetnames/ranges
  4. Save and close
  5. Tools -> Macro's -> Import -> Select: 'setImportRange'
  6. 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)`)

}

Upvotes: 1

Related Questions