CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsoptimizationgoogle-sheets-api
Phong Tran
Phong Tran

Reputation: 13

How to speed up copy large data from 1 file to another file?

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

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

Upvotes: 1

Views: 1162

Answers (1)

Tanaike
Tanaike

Reputation: 201398

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

References:

Upvotes: 6

Related Questions