Google Sheets Script - copy entire external spreadsheet to current spreadsheet

Question

I have google spreadsheet that updates weekly. It contains multiple sheets, which have different names from week to week.

Is there a way using google scripts to copy all of the sheets from that spreadsheet to another spreadsheet? My main goal is to copy the entire spreadsheet, and have the worksheet that I run the script from have all of the sheets and data from the source sheet. It would also be great if this copy process only copied the data, and not the formulas from the other sheet.

Thanks in Advance!

Eric

Hello Eric. Can you provide us some example, sample of code that you've already wrote ? — Pierre-Marie Richard

mattfippard mattfippard · Accepted Answer · 2017-05-11T14:32:32

I've written this to be ran from the source sheet but it can be easily adapted to run the other way round. This will only get the cell values not formulas and currently it creates a new spreadsheet with the date appended.

function copyEntireSpreadsheet(){
  var ss,ssName,sheet,sheetName,data,destination
  ss = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  ssName = SpreadsheetApp.getActive().getName();
  destination = SpreadsheetApp.create(ssName + " - " + new Date().toLocaleString());
  for (var i = 0; i < ss.length; i++){
    sheet = ss[i];
    sheetName = sheet.getSheetName();
    data = sheet.getSheetValues(1, 1, sheet.getLastRow(), sheet.getLastColumn());
    destination.insertSheet(sheetName)
    destination.getSheets()[i].getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).setValues(data);
  }
}

Because it inserts a sheet inside the loop you will get one blank sheet at the end of the script run but this could be deleted manually or at the end of the loop before the function closes.

Google Sheets Script - copy entire external spreadsheet to current spreadsheet

1 Answers