Java Script Optimization For A Google Apps Script

I have the working code below that compares two sets of data from two different sheets. One is a list of 395 phone numbers, the second is a list of around 135,000 rows of data. The

Solution 1:

• You want to compare the column "B" of "Sheet1" and the column "A" of "Sheet2".
• When the values of the column "B" of "Sheet1" and the column "A" of "Sheet2" are the same, you want to put the row of "Sheet1" to "Sheet3".
• You want to reduce the process cost of the script.
• You want to achieve this using Google Apps Script.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Modification points:

• Please use SpreadsheetApp.openById("1Aw11LiKzyezfrTQIuTsJPhUFtz8RPqLCc8FlIiy0ZlE") one time.
• In your case, values2 is used in the for loop. So values2 is looped every element of values1.
  • For this, an object is prepared for searching values.
• If the length of resultArray is large, the process cost can be reduced using Sheets API instead of Spreadsheet service like SpreadsheetApp. Ref

When above points are reflected to your script, the flow is as follows.

Flow:

1. Retrieve values from "Sheet1" and "Sheet2",
2. Create an object using values2.
3. Create resultArray using values1 and the object.
4. Put resultArray to "Sheet3" using the method of spreadsheets.values.update in Sheets API.

Modified script:

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

functioncopyRowtoSheet3() {
  var spreadsheetId = "1Aw11LiKzyezfrTQIuTsJPhUFtz8RPqLCc8FlIiy0ZlE";
  var ss = SpreadsheetApp.openById(spreadsheetId);
  var s1 = ss.getSheetByName('Sheet1');
  var s2 = ss.getSheetByName('Sheet2');

  // 1. Retrieve values from "Sheet1" and "Sheet2",var values1 = s1.getDataRange().getValues();
  var values2 = s2.getRange(1, 1, s2.getLastRow(), 1).getValues();

  // 2. Create an object using values2.var obj = values2.reduce((o, [e]) => {
    o[e] = null;
    return o;
  }, {});

  // 3. Create resultArray using values1 and obj.var resultArray = values1.filter(([,b]) => b in obj);

  // 4. Put resultArray to Sheet3.Sheets.Spreadsheets.Values.update({values: resultArray}, spreadsheetId, "Sheet3", {valueInputOption: "USER_ENTERED"});
}

Note:

• In this case, please enable V8 at the script editor.

References:

• Benchmark: Reading and Writing Spreadsheet using Google Apps Script
• Advanced Google services
• Method: spreadsheets.values.update

If I misunderstood your question and this was not the result you want, I apologize.