有沒有辦法優化這個腳本運行得更快？我收到錯誤“超過最大執行時間”

我正在使用 Google App Scripts 將資料從電子表格 #1 匯入電子表格 #2。但我收到錯誤“超過最大執行時間”。我究竟做錯了什么？有沒有更好的方法來優化代碼？

腳本邏輯

• 在電子表格 #1 上為 B 列創建陣列 #1，其中包含員工 ID
• 在電子表格 #2 上為 A 列創建陣列 #2，其中包含已匯入的員工 ID。
• 如果陣列 #1 包含陣列 #2 中未找到的 ID，則在電子表格 #2 上附加一個新行，其中包含 ID、email1 和 email2 列。

電子表格 #1（來源）

• 包含行中的重復資料。
• 目前有 50,000 行，每天新增 100 行。

電子表格 #2（目的地）

• 包含非重復行。

作業腳本（用于少于 5,000 行的資料）。

// Source sheet settings
let sourceSheetId = '1qW5UDn0O*******************';
let sourceTab = 'Source';
let sourceColumn = 2;
let sourceEmail1Column = 20;
let sourceEmail2Column = 19;

// Destination sheet settings
let destinationTab = 'Destination';
let destinationColumn = 1;
let destinationEmail1Column = 2;
let destinationEmail2Column = 4;


function newEmployeeIds() {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let destinationSheet = ss.getSheetByName(destinationTab);

  let sourceSS = SpreadsheetApp.openById(sourceSheetId);
  let sourceSheet = sourceSS.getSheetByName(sourceTab);

  let existingIdsArray = destinationSheet.getRange(2,destinationColumn,destinationSheet.getLastRow()-1,1).getValues();
  let existingIds = existingIdsArray.flat();

  let sourceIdArray = sourceSheet.getRange(2,sourceColumn,sourceSheet.getLastRow()-1,1).getValues();
  let sourceIds = [...new Set(sourceIdArray.flat())];

  let email1Array = sourceSheet.getRange(2,sourceEmail1Column,sourceSheet.getLastRow()-1,1).getValues();
  let email2Array = sourceSheet.getRange(2,sourceEmail1Column,sourceSheet.getLastRow()-1,1).getValues();

  for (i=0;i<sourceIds.length;i  ){
    if (existingIds.indexOf(sourceIds[i]) == -1){
    let newRow = destinationSheet.getLastRow() 1;
    destinationSheet.getRange(newRow,destinationColumn).setValue(sourceIds[i]);
    let index = sourceIdArray.flat().indexOf(sourceIds[i]);
    destinationSheet.getRange(newRow,destinationEmail1Column).setValue(email1Array[index][0]);
    destinationSheet.getRange(newRow,destinationEmail2Column).setValue(email2Array[index][0]);
    }
  }

}

uj5u.com熱心網友回復：

我相信你的目標如下。

• 您想降低腳本的處理成本。

在這種情況下，如何進行以下修改？在這個修改中，首先，從源表和目標表中檢索值，并創建一個用于放置到目標表的陣列，并將其放置到表中。

修改后的腳本：

function newEmployeeIds2() {
  const srcSheetName = 'Source';
  const dstSheetName = 'Destination';
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [srcSheet, dstSheet] = [srcSheetName, dstSheetName].map(s => ss.getSheetByName(s));

  // Retrieve values from source sheet.
  const srcValues = srcSheet.getDataRange().getValues();

  // Retrieve values from destination sheet and create an object for searching the ID.
  const dstLastRow = dstSheet.getLastRow();
  const dstObj = dstSheet.getRange("A1:A"   dstLastRow).getValues().reduce((o, [a]) => (o[a] = true, o), {});

  // Create an array for putting to the destination sheet.
  const values = srcValues.filter(r => !dstObj[r[1]]).map(r => [r[1], "", r[18], r[19]]);

  // Put the values to the destination sheet.
  dstSheet.getRange(dstLastRow   1, 1, values.length, values[0].length).setValues(values);
}

參考：

• 地圖（）
• 篩選（）
• 設定值（值）

添加：

從您的以下回復中，

不幸的是，我收到錯誤“無法讀取 null (const srcValues = srcSheet.getDataRange().getValues();) 的屬性 'getDataRange'”。我相信這是因為您的代碼假定源表和目標表在同一個電子表格中。實際上，源作業表位于不同的電子表格中。我嘗試更改 const srcSheetName = 'Source'; 到 const srcSheetName = SpreadsheetApp.openById('1qW5UDn0O************************').getSheetByName('Source'); 但運行代碼時仍然出錯。你知道有什么問題嗎？

根據您的回復，我將腳本修改如下。

修改后的腳本：

在運行腳本之前，請設定電子表格 ID1。

function newEmployeeIds2() {
  const srcSheetName = 'Source';
  const dstSheetName = 'Destination';
  const srcSheet = SpreadsheetApp.openById("SpreadsheetID1").getSheetByName(srcSheetName);

  const dstSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dstSheetName);
  // or const dstSheet = SpreadsheetApp.openById("SpreadsheetID2).getSheetByName(dstSheetName);

  // Retrieve values from source sheet.
  const srcValues = srcSheet.getDataRange().getValues();

  // Retrieve values from destination sheet and create an object for searching the ID.
  const dstLastRow = dstSheet.getLastRow();
  const dstObj = dstLastRow == 0 ? {} : dstSheet.getRange("A1:A"   dstLastRow).getValues().reduce((o, [a]) => (o[a] = true, o), {});

  // Create an array for putting to the destination sheet.
  const values = srcValues.filter(r => !dstObj[r[1]]).map(r => [r[1], "", r[18], r[19]]);

  // Put the values to the destination sheet.
  dstSheet.getRange(dstLastRow   1, 1, values.length, values[0].length).setValues(values);
}

標籤：

上一篇：應用腳本觸發器在Google觸發器儀表板中設定不同的時間

下一篇：如何使用GoogleApps腳本避免回圈中的空物件錯誤？