Java script optimization for a google apps script

Question

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 following code compares the two for similarities and copies the similar row to a third sheet.

While the code does work for small scale tests, (1000 rows and 395 phone numbers) in around 10 minutes. However doing 135,000 rows makes the script time out. Here is the script below.

`function copyRowtoSheet3() { 
  var s1 = SpreadsheetApp.openById("1Aw11LiKzyezfrTQIuTsJPhUFtz8RPqLCc8FlIiy0ZlE").getSheetByName('Sheet1');
  var s2 = SpreadsheetApp.openById("1Aw11LiKzyezfrTQIuTsJPhUFtz8RPqLCc8FlIiy0ZlE").getSheetByName('Sheet2'); 
  var s3 = SpreadsheetApp.openById("1Aw11LiKzyezfrTQIuTsJPhUFtz8RPqLCc8FlIiy0ZlE").getSheetByName('Sheet3'); 
  var values1 = s1.getDataRange().getValues();
  var values2 = s2.getDataRange().getValues();
  var resultArray = [];
  for(var n=0; n < values1.length ; n++){
    var keep = false;
    for(var p=0; p < values2.length ; p++){
      Logger.log(values1[n][0]+' =? '+values2[p][0]);
      if( values1[n][1] == values2[p][0] ){
        resultArray.push(values1[n]);
        Logger.log('true');
        //break ;
      }
    }
  }  
  s3.getRange(+1,1,resultArray.length,resultArray[0].length).setValues(resultArray);
}`

I'm not the best at optimizing code, and would love it if there were any suggestions that could be inputted here. I took a look at best practices and applied them already to the best of my abilities. Any suggestions to the code here?

around 10 minutes Really? For 1000 rows? Execution timeout is 6 minutes — TheMaster
– TheMaster, Commented Mar 3, 2020 at 8:00
From my understanding, for a paid business account time-out is 30 minutes. 6 minutes for unpaid. — Garrett Kidd
– Garrett Kidd, Commented Mar 3, 2020 at 16:38
Though that's true, it's highly unlikely that your script runs for 10 minutes for just 1k rows — TheMaster
– TheMaster, Commented Mar 3, 2020 at 17:03
The script runs the small scale test in 10 minutes. However, the script modifications that someone else provided to help, cut that time down to around 30 seconds. Cheers! — Garrett Kidd
– Garrett Kidd, Commented Mar 3, 2020 at 17:14

Tanaike · Accepted Answer · 2020-03-03 08:34:59Z

3

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:

Retrieve values from "Sheet1" and "Sheet2",
Create an object using values2.
Create resultArray using values1 and the object.
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.

function copyRowtoSheet3() {
  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:

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

answered Mar 3, 2020 at 8:34

Tanaike

204k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Garrett Kidd Over a year ago

Thank you for the detailed explanation of this! This is exactly what i was needing so u appreciate this. This really helped speed up the time on running this code and somehow managed to run all 135,000 rows compared to 395 phone numbers in just 20 minutes. Greatness!

Tanaike Over a year ago

@Garrett Kidd Thank you for replying and testing it. I'm glad your issue was resolved. Thank you, too.

Garrett Kidd Over a year ago

Tanaike, I have one more quick question about this script. Before i had the script compare the values of the column "B" of "Sheet1" and the column "A" of "Sheet2" and if they are the same, put the row of "Sheet1" to "Sheet3". How would i change it from selecting column B to selecting Column N in sheet 1 instead? I tried changing to this var resultArray = values1.filter(([,n]) => n in obj); but that did not work. ideas?

Tanaike Over a year ago

@Garrett Kidd About your new question, I would like to support you. But I have to apologize for my poor English skill. Unfortunately, I cannot understand about your new question. In order to correctly understand about your new question, can you post it as new question by including the detail information? By this, I would like to think of your new question. If you can cooperate to resolve your issue, I'm glad.

Garrett Kidd Over a year ago

Please take a look at the new post i made by your suggestion. stackoverflow.com/questions/60951952/…

|

Collectives™ on Stack Overflow

Java script optimization for a google apps script

1 Answer 1

Modification points:

Modified script:

Note:

References:

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modification points:

Modified script:

Note:

References:

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related