Google Apps Script: How to speed up copy large data from 1 file to another file?-CodePudding

I need your help to improve my apps script code in order to copy a dataset from 1 file to another file in Google Sheets. Currently it takes nearly 6 mins to finish this execution but our data is bigger by days. My details case is

In the source file, sheet Database with the range is A1:U11834. But the row will be increased days by days.
In the destination file, I also have a sheet name Database, and I want to clear the old data then copy the above source data into this sheet.

Here is my code.

function getdata(){

  let ss = SpreadsheetApp

  // open source file and sheet Database

  let source_file = ss.openById("id_source_file")
  let source_sht_copy = source_file.getSheetByName("Database")

  // Get full range of data
  let lr = source_sht_copy.getRange("A1").getDataRegion().getLastRow()
  let actual_range = `A1:U${lr}`

  Logger.log(actual_range)

  let source_data = source_sht_copy.getRange(actual_range).getValues()
  Logger.log("Copy Done")
  
  // Open destination file
  
  let dest_file = ss.openById("id_dest_file")
  let dest_sht = dest_file.getSheetByName("Database")

  // //clear content sheet database of destination file
  dest_sht.clearContents()
  Logger.log("Delete Old Data Done")

  
  // // paste data from source file to destination file using method 'setValues'
  dest_sht.getRange(actual_range).setValues(source_data)
  Logger.log("Paste Done")


}

And this is the image show the time of processing.

enter image description here

Hope that my words is clear and can get advices or suggestions from you guys.

Thanks a lot,

Have a nice day

PhongTran

CodePudding user response：

In your situation, in order to reduce the process cost, how about using Sheets API? When Sheets API is reflected in your script, it becomes as follows. When Sheets API is used, the process cost can be reduced than that of Spreadsheet services (SpreadsheetApp). Ref

Modified script:

Before you use this script, please enable Sheets API at Advanced Google services.

function getdata2() {
  const srcSpreadsheetId = "###"; // Please set source Spreadsheet ID.
  const dstSpreadsheetId = "###"; // Please set destination Spreadsheet ID.
  const srcRange = "'Database'!A1:U";
  const dstRange = "Database";

  const values = Sheets.Spreadsheets.Values.get(srcSpreadsheetId, srcRange).values;
  const sheetId = SpreadsheetApp.openById(dstSpreadsheetId).getSheetByName(dstRange).getSheetId();
  Sheets.Spreadsheets.batchUpdate({requests:[{repeatCell:{range:{sheetId},fields:"userEnteredValue"}}]}, dstSpreadsheetId);
  Sheets.Spreadsheets.Values.update({values}, dstSpreadsheetId, dstRange, {valueInputOption: "USER_ENTERED"});
}

Modified script:

References: