CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-api
xanderjames
xanderjames

Reputation: 1

Optimizing google apps script for replacing/appending values

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

Upvotes: 0

Views: 126

Answers (1)

Alan Wells
Alan Wells

Reputation: 31310

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.

Upvotes: 1

Related Questions