Google Apps脚本的Java脚本优化 [英] Java script optimization for a google apps script

问题描述

我下面有工作代码，用于比较来自两个不同工作表的两组数据.一个是395个电话号码的列表，第二个是大约135,000行数据的列表.下面的代码比较两者的相似性，然后将相似的行复制到第三张纸上.

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.

虽然该代码确实适用于小规模测试(大约1000行和395个电话号码)，但大约需要10分钟.但是，执行135,000行会使脚本超时.这是下面的脚本.

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?

推荐答案

• 您要比较"Sheet1"的"B"列和"Sheet2"的"A"列.
• 当"Sheet1"的"B"列和"Sheet2"的"A"列的值相同时，您要将"Sheet1"的行放入"Sheet3".
• 您要减少脚本的处理成本.
• 您想使用Google Apps脚本实现这一目标.

如果我的理解是正确的，那么这个答案如何?请认为这只是几个可能的答案之一.

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

• 请一次使用 SpreadsheetApp.openById("1Aw11LiKzyezfrTQIuTsJPhUFtz8RPqLCc8FlIiy0ZlE").
• 在您的情况下，在for循环中使用 values2 .因此， values2 被循环到 values1 的每个元素中.
  • 为此，准备了一个用于搜索值的对象.
  • 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.

    当以上几点反映到脚本中时，流程如下.

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

    1. 从"Sheet1"和"Sheet2"中检索值，
    2. 使用 values2 创建一个对象.
    3. 使用 values1 和对象创建 resultArray .
    4. 使用Sheets API中的sheets.values.update方法将 resultArray 放入"Sheet3".
    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.

    修改后的脚本:

    在使用此脚本之前，请请在Advanced Google启用Sheets API服务.

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

    注意:

    • 在这种情况下，请在脚本编辑器中启用V8.
    • 基准:使用Google Apps脚本读取和写入电子表格
    • 高级Google服务
    • 方法:sheets.values.update

    如果我误解了您的问题，而这不是您想要的结果，我深表歉意.

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

    这篇关于Google Apps脚本的Java脚本优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！