Google Apps Script - Improve Script to Execute Faster-CodePudding

I have a sheet with over 3000 points (lat, long). I am running the following code to calculate distance of each point from 9 reference points and writing the minimum distance next to each point in the sheet. The code runs fine but is slow and times out before running for all the points. How can I make the following code more efficient - so that it reads, computes and writes back the data faster?

/*
******************************
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;
};

Sample Spreadsheet Screenshot

CodePudding user response：

I believe your goal is as follows.

You want to reduce the process cost of your script.

In your script, getValue and setValue are used in a loop. I thought that this might be the reason for your issue. In this case, at first, the values are retrieved from the sheet and the values are processed, then, the values are put to the sheet. This flow is suitable for reducing the process cost. I think that "Best Practice" will be useful for understanding your issue. Ref

Also, I thought that when the process cost of your script is reduced, a little ingenuity might be required to be used for modifying your script. So I propose one of several modified scripts as a reference. Please modify your script as follows.

From:

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

}

To:

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

In this modification, from your script, it supposes that the 1st row is not the header row. Please be careful about this.
In this modification, the values are retrieved from the sheet by getValues, and then, the retrieved values are calculated and put to the sheet by setValues.

Note:

When I saw your script, I thought that an error occurs at var Lat_Check = sheet.getRange(i,2).getValue(); because the 1st value of i is 0. And in the function calculateDistance, Ref_Lat, Ref_Long are not used. So I'm worried that your showing script might be different from the script that you have the issue with. So, when you test the above modification, when an error occurs, can you confirm whether your showing script is the correct script for your situation. And, please provide the sample Spreadsheet for replicating the issue as the image. By this, I would like to confirm it.

From:

To:

Note:

References: