Import Data from Multiple Different Google Workbooks to One Master Workbook-CodePudding

I'm looking for a way to import data from 57 Google Sheets Files / Workbooks to one master Google Sheet. For example, I have 57 teachers - each with their own Google Sheets document. I use their document when I observe to collect data. I need a way to grab the data I collected from each of their separate files, bring it into my master file and match it with their name in my sheet without having to use the IMPORTRANGE function for each one. Is this possible with a script, or is there any easier way to do this?

CodePudding user response：

Try (change id in the list)

const ids = [
  '1T7ixa-JGLgh8oZ19mWUy3Oo4UzbivflUlwL9zLGVkgg',
  '16uEeVNEBzBbGVTgKa98Ob7Wcy5mof9n6mU-yKIZ0fKM',
  '1d3ZHi1H0uUMU7jYsrwses66ULkmM4bFHyDn8RFtAlkk'
]
function myFunction() {
  let result = []
  ids.forEach((id, i) => {
    let [headers, ...data] = SpreadsheetApp.openById(id).getSheets()[0].getDataRange().getValues()
    if (i == 0) { result.push(headers.flat()) }
    data.forEach(r => result.push(r))
  })
  var master = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('myMasterSheet') 
  master.clearContents()
  master.getRange(1, 1, result.length, result[0].length).setValues(result)
}

you can use a sheet to store the IDs if necessary