How to copy and paste as values Google Apps Script/Sheets from one spreadsheet to another?

user_8356263 0
Viewed 901

So I have a huge spreadsheet with 9k rows and more than 30 columns. I want to copy this spreadsheet to another spreadsheet (Values only).

Previously I was using this code successfully, but due to the increase in data, the script now times out (1800s +). Is there a way to optimize this script or maybe an alternative option altogether?

function temp() {

  var sss = SpreadsheetApp.openById('XYZ'); // sss = source spreadsheet
  //var ss = sss.getSheets()[4]; // ss = source sheet

  var ss = sss.getSheets(); // ss = source sheet
  var id=4; //default number

  for(var i in ss)
  {
    var sheet = ss[i];
    if(sheet.getName()== "ABC")
    {  id=i;
      break;
    }
  }
  console.log(id);
  
  ss=sss.getSheets()[id];

  //Get full range of data
  var SRange = ss.getDataRange();
  //get A1 notation identifying the range
  var A1Range = SRange.getA1Notation();
  //get the data values in range
  var SData = SRange.getValues();
  SpreadsheetApp.flush();
  var tss = SpreadsheetApp.getActiveSpreadsheet(); // tss = target spreadsheet
  var ts = tss.getSheetByName('ABC'); // ts = target sheet
  //set the target range to the values of the source data
  ts.getRange(A1Range).setValues(SData);


}
google-apps-script google-sheets-api google-sheets
2 Answers

I believe your goal is as follows.

  • You want to reduce the process cost of the script.

In this case, I would like to propose to use Sheets API. This has already been mentioned in the Yuri Khristich's comment. Also, when the benchmark is measured between Spreadsheet service (SpreadsheetApp) and Sheets API, when Sheets API is used for reading and writing the values for Spreadsheet, it was confirmed that the process cost could be reduced. Ref

When Sheets API is used for your script, it becomes as follows.

Modified script:

Before you use this script, please enable Sheets API at Advanced Google services. And please set the source Spreadsheet ID and the sheet names.

function temp() {
  var sourceSpreadsheetId = "XYZ"; // Please set the source Spreadsheet ID.
  var destinationSpreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
  var sourceValues = Sheets.Spreadsheets.Values.get(sourceSpreadsheetId, "ABC").values;
  Sheets.Spreadsheets.Values.update({values: sourceValues}, destinationSpreadsheetId, "ABC", {valueInputOption: "USER_ENTERED"});
}

References:

user_71086530

Copy from one spreadsheet to another

function copyfromonetoanother() {
  const sss = SpreadsheetApp.getActive();
  const dss = SpreadsheetApp.openById("dssid");
  const ssh = sss.getSheetByName('Sheet1');
  const vs = ssh.getDataRange().getValues();
  const dsh = dss.getSheetByName('Sheet1');
  dsh.getRange(dsh.getLastRow() + 1,1,vs.length,vs[0].length).setValues(vs);
}

If you wish to select the source range:

function copyfromonetoanother() {
  const sss = SpreadsheetApp.getActive();
  const dss = SpreadsheetApp.openById("dssid");
  const ssh = sss.getSheetByName('Sheet1');
  const vs = ssh.activeRange().getValues();
  const dsh = dss.getSheetByName('Sheet1');
  dsh.getRange(dsh.getLastRow() + 1,1,vs.length,vs[0].length).setValues(vs);
}

This function appends to the bottom of the destination sheet

function appenddatatobottomofdestination() {
  const sssId = "source spreadsheet id"; 
  const sss = SpreadsheetApp.openById(sssId);
  const dss = SpreadsheetApp.getActive();
  const dssId = dss.getId();
  const ssh = sss.getSheetByName('Sheet1');//Source sheet
  const srg = ssh.getRange(1,1,ssh.getLastRow(),ssh.getLastColumn());
  const dsh = dss.getSheetByName("Sheet1");//Destination sheet
  var vs = Sheets.Spreadsheets.Values.get(sssId, `${ssh.getName()}!${srg.getA1Notation()}`).values;
  const drg = dsh.getRange(dsh.getLastRow() + 1, 1, vs.length,vs[0].length);//appends to bottom of spreadsheet
  Sheets.Spreadsheets.Values.update({values: vs}, dssId, `${dsh.getName()}!${drg.getA1Notation()}`, {valueInputOption: "USER_ENTERED"});
}
user_72150910
Related