CODEWITHSUNDEEP
algorithmsortinggoogle-apps-scriptgoogle-sheets
Negimaki
Negimaki

Reputation: 27

Speed Up Find-and-Replace Google Apps Script Function for sheets

I've written a pretty simple script that successfully takes information from one sheet in a Google Spreadsheet, and replaces information in a column in another sheet in the same spreadsheet pending satisfaction of two criteria: the receiving row has the same "Customer ID" and "Product Type." I say "simple" because it's intuitive, but extremely computationally demanding (taking nearly 30 seconds to run!).

From what I've read online, it's the sequential read and write operations that are causing the slowdown. I'm assuming that if I sort the sheets in question on the two criteria and THEN do a function that writes over subsequent rows, I may be able to speed it up. I'm a little weak on algorithms, so I'm still scratching my head on how to do this elegantly.

Does anyone have any suggestions? Below is my original script, and I've already made sure that the spreadsheet collapses empty rows, so time isn't wasted iterating over nothing.

function replaceRawWithRepChanges(receivedSheet) {
  var ss = SpreadsheetApp.openById(receivedSheet);

  var repchanges = ss.getSheetByName('repchanges');
  var rawSheet = ss.getSheetByName('Sheet1');

  var rawTMtoReplace = rawSheet.getRange('P2:P');

  var repCustID = repchanges.getRange('A1:A').getValues();
  var repTM = repchanges.getRange('F1:F').getValues();
  var repCategory = repchanges.getRange('G1:G').getValues();

  var rawCustID = rawSheet.getRange('A2:A').getValues();
  var rawTM = rawSheet.getRange('P2:P').getValues();
  var rawCategory = rawSheet.getRange('U2:U').getValues();

  var repInfo = [repCustID, repTM, repCategory];
  var rawInfo = [rawCustID, rawTM, rawCategory];

  for (var i=0; i < rawInfo[0].length; i++) {
    for (var j=0; j < repInfo[0].length; j++) {
//      var thisRawCust = rawInfo[0][i];
//      var thisRepCust = repInfo[0][j];
      if (rawInfo[0][i].toString() == repInfo[0][j].toString()) {
//        var thisRawCategory = rawInfo[2][i];
//        var thisRepCategory = repInfo[2][j];
        if (rawInfo[2][i].toString() == repInfo[2][j].toString()) {
//          var repvalue = repInfo[1][j];
          rawInfo[1][i] = repInfo[1][j];
//          var newRawValue = rawInfo[1][i];
        }
      }
    }
  }

  return rawInfo[1];
}

Upvotes: 0

Views: 221

Answers (1)

MattS
MattS

Reputation: 138

Yes, you should sort the data (perhaps using the SORT command, which does work with multiple columns). Then, using two pointers, you only have to go down the columns once, rather than checking the entirety of repInfo for matches for every single row in rawInfo.

Once you've sorted the information, your loop might look like the following:

var i = 0;
var j = 0;
while (i < rawInfo[0].length && j < repInfo[0].length) {
  if (rawInfo[0][i].toString() == repInfo[0][j].toString()) {
    if (rawInfo[2][i].toString() == repInfo[2][j].toString()) {
      rawInfo[1][i]=repInfo[1][j];
      i++;
      j++;
    } else if (rawInfo[2][i].toString() < repInfo[2][j].toString()) {
      i++;
    } else {
      j++;
    }
  } else if (rawInfo[0][i].toString() < repInfo[0][j].toString()) {
    i++;
  } else {
    j++;
  }
}

Upvotes: 2

Related Questions