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

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);


}

CodePudding user response：

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);
}

CodePudding user response：

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"});
}

Copy from one spreadsheet to another

Modified script:

References: