1

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

Rubén
  • 34,714
  • 9
  • 70
  • 166
Phong Tran
  • 13
  • 3
  • I proposed a workaround using Sheets API. Could you please confirm it? But, if that was not useful for your situation, I apologize. – Tanaike Mar 10 '22 at 12:28
  • Thx Tanaike for your proposal. I am interested in. Hi @PhongTran, can you give the benchmark between your present script and the proposal? – Mike Steelson Mar 10 '22 at 13:02
  • @MikeSteelson, the code of Tanaike took only around 15s to finish the process compare to 6min of my code. – Phong Tran Mar 10 '22 at 13:16

1 Answers1

6

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:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • 1
    your code and your instructions are perfect to me. Thanks a lot for your help. Have a nice day. – Phong Tran Mar 10 '22 at 13:05
  • 2
    beautiful, upvoted! – Mike Steelson Mar 10 '22 at 13:50
  • @Tanaike I read your suggestion and found the accept button. Thanks a lot. By the way, I wondering that the API can reduce the process cost of combining multiple files in one folder into 1 file or not. If you don't mind please let me know if possible. Thanks a lot. Have a nice day! – Phong Tran Mar 12 '22 at 12:06
  • I have a column in this import range that has date format, how do I keep this format after import? – Sidney Cardoso dos Santos Oct 21 '22 at 21:05
  • @Sidney Cardoso dos Santos I would like to support you. But, this is not your question. So, can you post it as a new question? By this, it will help users including me think of the solution. When you can cooperate to resolve your question, i'm glad. Can you cooperate to do it? – Tanaike Oct 21 '22 at 23:18