Optimizing google apps script for replacing/appending values

Question

I have a function that loops through array C:D to find a match in A:B, if there is it replaces the value in B with D and if there's no match it appends C:D to A:B. This function is using loops. I know there's a way to optimize this, but I'm lost. How else can this script run without loops?

function moveValues() {
  var ss = SpreadsheetApp.openById('open_id');
  var source = ss.getRange('sheet2!D:C');
  var destination = ss.getRange('sheet2!A:B');
  var destCount = 0;
  for (var j = 1; j <= destination.getLastRow(); j++) {
if (destination.getCell(j,1).getValue() == "") {
  destCount = j;
  break;
 }
}
for (var i = 1; i <= source.getLastRow(); i++) {
Logger.log(source.getLastRow());
var added = false;
var targetName = source.getCell(i,1).getValue();
var copyValue = source.getCell(i,2).getValue();
if (targetName == "") {
  break; 
}
for (var j = 1; j <= destCount; j++) {
  var curName = destination.getCell(j,1).getValue();
  if (copyValue != "" && targetName == curName) {
    destination.getCell(j, 2).setValue(copyValue);
    added = true;
    break;
  } 
}
if (!added) {
  destination.getCell(destCount, 1).setValue(targetName);
  destination.getCell(destCount, 2).setValue(copyValue);
  destCount += 1;
 }
}
source.clear();
};

Every getCell api call takes time, so it's much faster to use .getValues() only once, which returns a 2-dimensional array containing the values of a range. Also set the new values with .setValues(newValues) on a range, only one api call at the end. — Wim den Herder

Alan Wells Alan Wells · Accepted Answer · 2017-02-14T23:04:01

You will still need to use loop(s), but the code can be optimized. Use getValues() at the beginning. That returns a 2D array. You can use .indexOf() to determine whether there is a match in the other array.

function moveValues() {
  var i,L,sh,ss,srcRng,destRng,srcData,targetData,v;

  ss = SpreadsheetApp.openById('open_id');
  sh = ss.getSheetByName('sheet2');//Get sheet2
  lastRow = sh.getLastRow();//Get the row number of the last row

  srcRng = sh.getRange(1,1,lastRow);//Get the range for all the values in column 1
  destRng = sh.getRange(3,1,lastRow);//Get the range for all the values in column 3

  srcData = srcRng.getValues();//Get a 2D array of values
  targetData = destRng.getValues();//Get a 2D array of values

  srcData = srcData.toString().split(",");//Convert 2D to 1D array
  targetData = targetData.toString().split(",");//Convert 2D to 1D array

  L = srcData.length;

  for (i=0;i<L;i++) {//Loop the length of the source data
    v = srcData[i];//Get this value in the array
    if (targetData.indexOf(v) !== -1) {//This value was found in target array

    }
  }

This is not a complete answer to your question, but hopefully it will give you some ideas.

In this example the code is getting just the columns of data to compare, and not the columns of data to change.

Optimizing google apps script for replacing/appending values

1 Answers