Today I create a daily report in Google Sheets, where I compile data from multiple sources. One of the source is a Google Drive folder, where another team uploads a Google sheet on a daily basis. I'm looking for a script/importrange technique to pull the data from the last modified file in that folder into my daily report.

The Google Drive Folder path remains unchanged, however the Google sheet name changes each day

Let me know if there is anyway to automate this copy and pasting of reports on daily basis

Thanks

Tried to use google script, but I could not use importrange for dynamic destination path.

CodePudding user response：

If i understand you're question correctly you want:

1. Get the data from the last created google spreadsheet.
2. From a specific drive folder
3. Copy the data into you're Daily Report.

If that is the case, then this script will help you out:

1. Go to Extensions -> Apps script in the spreadsheet with the Daily report sheet.
2. Paste the code from below
3. Change the Drive folder id (last part of the url)
4. Change the target sheetname (i assume it is always the same name?)
5. Change (if needed) the tab name of the daily report. (case sensitive)
6. Hit run and give permission
7. Set trigger via the clock icon to run daily on a specific time.

Be aware there is no logic to not run on weekends... nor it checks if the data is already imported. But this script will get you started.

function getLastFileFromDriveFolder() {
  const folder = DriveApp.getFolderById("xxxxx")
  const files = folder.getFilesByType(MimeType.GOOGLE_SHEETS);

  let date = 0
  let spreadsheetId

  //Find the latest created file
  while (files.hasNext()) {
    const file = files.next()
    const created = file.getDateCreated().getTime()

    if (created > date) {
      date = created
      spreadsheetId = file.getId()
    }
  }

  //If no file is found return
  if (!spreadsheetId) {
    return;
  }

  //Open the input sheet that is found in the loop above
  const inputSpreadSheet = SpreadsheetApp.openById(spreadsheetId)
  const inputSheet = inputSpreadSheet.getSheetByName("TheNameOfTheSheetTab")
  //Get data from row 2 (so skip headers??) otherwise change 2 to 1 and remove the -1
  const data = inputSheet.getRange(2, 1, inputSheet.getLastRow() - 1, inputSheet.getLastColumn()).getValues()

  //Get your bound spreadsheet and the target sheet.
  const targetSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  const targetSheet = targetSpreadsheet.getSheetByName("Daily report")

  //Paste in the next empty row.
  targetSheet.getRange(targetSheet.getLastRow()   1, 1, data.length, data[0].length).setValues(data)

}