GoogleApps腳本-改進腳本以更快地執行-有解無憂

我有一張超過 3000 點（經緯度）的作業表。我正在運行以下代碼來計算每個點與 9 個參考點的距離，并在作業表中的每個點旁邊寫下最小距離。代碼運行良好，但在運行所有點之前很慢并且超時。如何使以下代碼更高效 - 以便更快地讀取、計算和寫回資料？

/*
******************************
Reference Points        
Point_A 41.166866   -76.151926
Point_B 41.087500   -76.204694
Point_C 40.540960   -75.704900
Point_D 40.401080   -75.589600
Point_E 40.326130   -75.642500
Point_F 40.167480   -75.921500
Point_G 40.093370   -76.084700
Point_H 39.974450   -76.063000
Point_I 39.722350   -76.156000
********************************
*/

var SpreadsheetID = "ABC";
var SheetName = "XYZ";

function DistanceCalculator() {
  var ss = SpreadsheetApp.openById(SpreadsheetID)
  var sheet = ss.getSheetByName(SheetName);


  //Select the column
  var columnToCheck = sheet.getRange("A:A").getValues();
  
  // Get the last row in the column
  var lastRow = getLastRowSpecial(columnToCheck);
  
  // Ref point latitude
  var Latitude_Point_A = 41.166866;
  var Latitude_Point_B = 41.087500;
  var Latitude_Point_C = 40.540960;
  var Latitude_Point_D = 40.401080;
  var Latitude_Point_E = 40.326130;
  var Latitude_Point_F = 40.167480;
  var Latitude_Point_G = 40.093370;
  var Latitude_Point_H = 39.974450;
  var Latitude_Point_I = 39.722350;

  // Ref point longitude
  var Longitude_Point_A = -76.151926;
  var Longitude_Point_B = -76.204694;
  var Longitude_Point_C = -75.704900;
  var Longitude_Point_D = -75.589600;
  var Longitude_Point_E = -75.642500;
  var Longitude_Point_F = -75.921500;
  var Longitude_Point_G = -76.084700;
  var Longitude_Point_H = -76.084700;
  var Longitude_Point_I = -76.156000;
  
  for( var i=1; i<=lastRow;i  ){
    //Reading Lat_Long from the sheet
    var Lat_Check = sheet.getRange(i,2).getValue();
    var Long_Check = sheet.getRange(i,3).getValue();

    //Calculating distance between each point and reference points
    var distance01 = calculateDistance(Latitude_Point_A,Longitude_Point_A,Lat_Check,Long_Check);
    var distance02 = calculateDistance(Latitude_Point_B,Longitude_Point_B,Lat_Check,Long_Check);
    var distance03 = calculateDistance(Latitude_Point_C,Longitude_Point_C,Lat_Check,Long_Check);
    var distance04 = calculateDistance(Latitude_Point_D,Longitude_Point_D,Lat_Check,Long_Check);
    var distance05 = calculateDistance(Latitude_Point_E,Longitude_Point_E,Lat_Check,Long_Check);
    var distance06 = calculateDistance(Latitude_Point_F,Longitude_Point_F,Lat_Check,Long_Check);
    var distance07 = calculateDistance(Latitude_Point_G,Longitude_Point_G,Lat_Check,Long_Check);
    var distance08 = calculateDistance(Latitude_Point_H,Longitude_Point_H,Lat_Check,Long_Check);
    var distance09 = calculateDistance(Latitude_Point_I,Longitude_Point_I,Lat_Check,Long_Check);


    //measuring minimum distance
    var minimumDistance = Math.round(Math.min(distance01,distance02,distance03,distance04,distance05,distance06,distance07,distance08,distance09))

    sheet.getRange(i,4).setValue(minimumDistance); 

  }
  
}


function calculateDistance(Ref_Lat, Ref_Long, Looped_Lat, Looped_Long){

  var p = 0.017453292519943295;    // Math.PI / 180
  var c = Math.cos;
  var a = 0.5 - c((Looped_Lat - Ref_Lat) * p)/2   
          c(Ref_Lat * p) * c(Looped_Lat * p) * 
          (1 - c((Looped_Long - Ref_Long) * p))/2;
  var result = 7926 * Math.asin(Math.sqrt(a)); // 2 * R; R = 3963 miles
  return result
};


function getLastRowSpecial(range){
  var rowNum = 0;
  var blank = false;
  for(var row = 0; row < range.length; row  ){
 
    if(range[row][0] === "" && !blank){
      rowNum = row;
      blank = true;
    }else if(range[row][0] !== ""){
      blank = false;
    };
  };
  return rowNum;
};

示例電子表格螢屏截圖

uj5u.com熱心網友回復：

我相信你的目標如下。

您希望降低腳本的流程成本。

在您的腳本中，getValue并setValue在回圈中使用。我認為這可能是您的問題的原因。在這種情況下，首先從作業表中檢索值并處理這些值，然后將這些值放入作業表。該流程適用于降低工藝成本。我認為“最佳實踐”將有助于理解您的問題。參考

另外，我認為當您的腳本的處理成本降低時，可能需要使用一些獨創性來修改您的腳本。所以我提出了幾個修改過的腳本之一作為參考。請按如下方式修改您的腳本。

從：

for( var i=0; i<lastRow;i  ){
  //Reading Lat_Long from the sheet
  var Lat_Check = sheet.getRange(i,2).getValue();
  var Long_Check = sheet.getRange(i,3).getValue();

  //Calculating distance between each point and reference points
  var distance01 = calculateDistance(Latitude_Point_A,Longitude_Point_A,Lat_Check,Long_Check);
  var distance02 = calculateDistance(Latitude_Point_B,Longitude_Point_B,Lat_Check,Long_Check);
  var distance03 = calculateDistance(Latitude_Point_C,Longitude_Point_C,Lat_Check,Long_Check);
  var distance04 = calculateDistance(Latitude_Point_D,Longitude_Point_D,Lat_Check,Long_Check);
  var distance05 = calculateDistance(Latitude_Point_E,Longitude_Point_E,Lat_Check,Long_Check);
  var distance06 = calculateDistance(Latitude_Point_F,Longitude_Point_F,Lat_Check,Long_Check);
  var distance07 = calculateDistance(Latitude_Point_G,Longitude_Point_G,Lat_Check,Long_Check);
  var distance08 = calculateDistance(Latitude_Point_H,Longitude_Point_H,Lat_Check,Long_Check);
  var distance09 = calculateDistance(Latitude_Point_I,Longitude_Point_I,Lat_Check,Long_Check);


  //measuring minimum distance
  var minimumDistance = Math.round(Math.min(distance01,distance02,distance03,distance04,distance05,distance06,distance07,distance08,distance09))

  sheet.getRange(i,4).setValue(minimumDistance); 

}

到：

var values = sheet.getRange("B1:C"   lastRow).getValues();
var ar = [[Latitude_Point_A, Longitude_Point_A], [Latitude_Point_B, Longitude_Point_B], [Latitude_Point_C, Longitude_Point_C], [Latitude_Point_D, Longitude_Point_D], [Latitude_Point_E, Longitude_Point_E], [Latitude_Point_F, Longitude_Point_F], [Latitude_Point_G, Longitude_Point_G], [Latitude_Point_H, Longitude_Point_H], [Latitude_Point_I, Longitude_Point_I]];
var res = values.map(([b, c]) => [Math.round(Math.min(...ar.map(e => calculateDistance(...e, b, c))))]);
sheet.getRange(1, 4, res.length).setValues(res);

在此修改中，根據您的腳本，它假設第一行不是標題行。請注意這一點。
在這個修改中，值是從作業表中檢索出來的getValues，然后，檢索到的值被計算出來并放到作業表中setValues。

筆記：

當我看到你的腳本時，我認為發生了錯誤，var Lat_Check = sheet.getRange(i,2).getValue();因為的第一個值i是0。而在函式中calculateDistance，Ref_Lat, Ref_Long則沒有使用。所以我擔心您的顯示腳本可能與您遇到問題的腳本不同。所以，當你測驗上述修改的時候，出現錯誤的時候，你能不能確認一下你的顯示腳本是否是適合你情況的正確腳本。并且，請提供示例電子表格以將問題復制為影像。借此，我想確認一下。

參考：

地圖（）
獲取值（）
設定值（值）

轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/381092.html

標籤：javascript 谷歌应用程序脚本谷歌表格

上一篇：如何將帶邊框的文本放在導航選單文本下方？

下一篇：根據按下的按鈕填充不同顏色的畫布