如何在不獲得運行時超時的情況下改進for回圈，

我目前正在開展一個專案，該專案交叉驗證 2 張大約有 500 個回圈的作業表。

名冊

REGISTRATION

RESULT

Sample Data Sheet my problem is that my script sometimes getting run-time timeout. I am seeing map function but I'm not sure if it's applicable for my current problem.

here are my sheets that is included in my script. sheets are roster, form responses 1, reference, result. roster and registration tab have common columns (First name, Last Name, Date of Birth)

var SS              = SpreadsheetApp.getActiveSpreadsheet()
var rosterTab       = SS.getSheetByName('Roster')
var registrationTab = SS.getSheetByName('Responses 1')
var referenceTab    = SS.getSheetByName('Reference')
var resultTab       = SS.getSheetByName('Result') //this is where I posts all registered names that is on the roster tab.
var xLastRow        = registrationTab.getLastRow();
var yLastRow        = rosterTab.getLastRow();
var Data            = [];
var codeStart       = referenceTab.getRange('A1').getValue();
var rosterdata      = rosterTab.getRange(1,1,300,3).getValues();
var registerdata    = registrationTab.getRange(1,1,xLastRow,3).getValues();

for(var i = 0; i<yLastRow; i  (){
    for(var j = 0; j<xLastRow; j  (){
        if (rosterdata[i][0] === registerdata[j][0] && rosterdata[i][1] === registerdata[j][1] && rosterdata[i][2] === registerdata[j][2]){
            var rosterFname = registerdata[j][0];
            var rosterLname = registerdata[j][1];
            var rosterDoB   = registerdata[j][2];
            var rosterCode  = codeStart;

            Data.push([rosterFname,rosterLname,rosterDoB,rosterCode]);

            break;
        }
    }
}
resultTab.getRange( resultTab.getLastRow() 1, 1, Data.length,Data[0].length ).setValues(Data);

Is there another way on how to approach this matching without getting timed-out?

uj5u.com熱心網友回復：

function myfunky11() {
  const ss = SpreadsheetApp.getActive();
  const rosterTab = ss.getSheetByName('Roster');
  const registrationTab = ss.getSheetByName('Responses 1');
  const referenceTab = ss.getSheetByName('Reference');
  let rsh = ss.getSheetByName('Result'); 
  var data = [];
  const codeStart = referenceTab.getRange('A1').getValue();
  const ros = rosterTab.getRange(2, 1, rosterTab.getLastRow() - 1, 3).getDisplayValues().map(r => r[0]   r[1]   r[2]);
  const reg = registrationTab.getRange(2, 1, registrationTab.getLastRow() - 1, 3).getValues();
  reg.forEach(r => {
    let s = r[0].toString()   r[1].toString()   Utilities.formatDate(new Date(r[2]), Session.getScriptTimeZone(), "M/d/yyyy");
    if (ros.indexOf(s)>-1) {
      r.push(codeStart);
      data.push(r)
    }
  });
  rsh.clear();
  rsh.getRange(1, 1, data.length, data[0].length).setValues(data);
}

這現在有效

uj5u.com熱心網友回復：

您可以通過跟蹤已處理的最后一行來批量處理所有“名冊”資料 PropertiesService

const LAST_PROCESSED_ROW_PROPERTY_KEY = 'lastProcessedRow';
const PROCESS_ROW_PER_BATCH           = 100;

var scriptProperties = PropertiesService.getScriptProperties();
var lastProcessedRow = scriptProperties.getProperty( LAST_PROCESSED_ROW_PROPERTY_KEY );

if ( lastProcessedRow === null ) {
    lastProcessedRow = 0;
}

// Your variables here

var yLastRow          = rosterTab.getLastRow();
var rowsToBeProcessed = PROCESS_ROW_PER_BATCH;

if ( lastProcessedRow == yLastRow ) {
    Logger.log( 'No new data to be processed' );

    return;
}

if ( yLastRow - lastProcessedRow < PROCESS_ROW_PER_BATCH ) {
    rowsToBeProcessed = yLastRow - lastProcessedRow;
}

var rosterdata = rosterTab.getRange( lastProcessedRow   1, 1, rowsToBeProcessed, 3 ).getValues();

for( var i = 0; i < rosterdata.length; i   ) {
    // Your data processing logic here
}

// Set data logic here

scriptProperties.setProperty( LAST_PROCESSED_ROW_PROPERTY_KEY, lastProcessedRow   rowsToBeProcessed );

uj5u.com熱心網友回復：

我相信你的目標如下。

• 您的電子表格中有 3 張紙。
• 您想從“Form Response 1”和“Roster”表中檢索值。當“Form Response 1”表中“B”到“D”列的值與“Roster”表中“A”到“C”列的值相同時，您希望將它們放入“Result”表中附加值。
• 您希望降低此流程的流程成本。

在這種情況下，以下修改后的腳本如何？

修改后的腳本：

function myFunction() {
  var SS = SpreadsheetApp.getActiveSpreadsheet();
  var rosterTab = SS.getSheetByName('Roster');
  var registrationTab = SS.getSheetByName('Form Response 1');
  var referenceTab = SS.getSheetByName('Reference');
  var resultTab = SS.getSheetByName('Result');
  var xLastRow = registrationTab.getLastRow();
  var yLastRow = rosterTab.getLastRow();
  var Data = [];
  var codeStart = referenceTab.getRange('A1').getValue();
  var rosterdata = rosterTab.getRange(1, 1, yLastRow, 3).getValues();
  var registerdata = registrationTab.getRange(2, 2, xLastRow - 1, 3).getValues();

  // I modified below script.
  // Create an object.
  var obj = rosterdata.reduce((o, [a,b,c]) => (o[a   b   c] = true, o), {});

  // Create an array for putting to sheet.
  var values = registerdata.reduce((ar, [a, b, c]) => {
    if (obj[a   b   c]) ar.push([a, b, c, codeStart]);
    return ar;
  }, []);
  
  // Put the array to the result sheet.
  if (values.length == 0) return;
  resultTab.getRange(resultTab.getLastRow()   1, 1, values.length, values[0].length).setValues(values);
}

• 在此修改中，創建一個用于搜索值的物件并創建一個用于放入作業表的陣列，并將創建的陣列附加到結果作業表中。通過這個流程，我認為工藝成本可能會降低一點。

參考：

• 降低（）

標籤：google-apps-script google-sheets

上一篇：如何獲取GoogleDrivesAPI的files.listAPI端點的pageToken？[復制]

下一篇：例外：引數(String,String,String,(class),(class))與GmailApp.sendEmail的方法簽名不匹配