Khaoz Khaoz - 2 months ago 7
Javascript Question

Refining Data 'Fetch/Match' in Google Scripts

I have some code that is working fine, just rather slow and clunky. Im sure there is a better way of doing this:

I have two sheets, one which the user edits ('MAIN') and one which the script pulls data from (over 5000 lines of data) ('REF'). Basically it checks for a match on column 1 and if it matches what the user has entered, it grabs the value from the same row but column 3.

The code works, but rather slowly:

function onEdit(){
var s = SpreadsheetApp.getActive().getSheetByName('MAIN');
var sref = SpreadsheetApp.getActive().getSheetByName('REF');

var activeRow = s.getActiveCell().getRow();
var activeCol = s.getActiveCell().getColumn();
var activeCell = s.getActiveCell().getValue();

var mainPageBranch = s.getRange(8,1).getValue();
var refPageBranch = sref.getRange('I4').getValue();

var lastrow = sref.getLastRow();

if(activeCol == '2' || activeCol == '5' || activeCol == '8' || activeCol == '11' || activeCol == '14' || activeCol == '17' || activeCol == '20'){
if(activeRow > 9 && activeRow < 41){
if(activeCell > 100000){
s.getRange(activeRow, activeCol+1).setValue(1);
}
else{
s.getRange(activeRow, activeCol+1).setValue('');
}
for(var i=5; i <lastrow; i++){
var productCode = sref.getRange(i, 1).getValue();
if(activeCell == productCode){
var essMould = sref.getRange(i,3).getValue();
s.getRange(activeRow+1,activeCol).setValue(essMould);
s.getRange(activeRow+1,activeCol+1).setValue('1');
break;
}
}
Logger.log('Product Code: ' + productCode);
Logger.log('Ess Mould: ' + essMould);
Logger.log('Last Row: ' + lastrow);
}
}
}


I understand it's kind of clunky - any help making it faster would be great!

Let me know if you need anything else :)

Answer

You should not be getting individual values on every loop:

for(var i=5; i <lastrow; i++){
  var productCode = sref.getRange(i, 1).getValue();

First get all the values, loop through the array of data, change the data in the array if needed, and then write the entire column of data back to the spreadsheet.

var data,essMould,i,L,productCode;

data = sref.getRange(5, 1, lastrow-5,activeCol).getValues();
L = data.length; //Length of data

for(i=0; i < L; i++){
  productCode = data[i][0];
  Logger.log("productCode: " + productCode);

  if (activeCell == productCode) {
    essMould = data[i][activeCol];
    data[i][2] = essMould;
    data[i + 1][activeCol + 1] = 1;
    break;
  }
}

sref.getRange(5,1,data.length,data[0].length).setValues(data);
Comments