Is there an efficient way to search for and append a string within a column of cells in google apps script?

Question

As the title says, I'm currently running a function that I think could be more efficient.

Essentially, I have a column (k) in my spreadsheet that I am looking for the text "delete" in. If that text is found, then I simply append ", removed" to it. This lets the Appsheet running off of the spreadsheet know not to display that row if it sees the text "removed".

For example, a random row of col k might look like this after the function is run: delete, completed, removed

The issue here is that our sheet has 4000 rows and it is taking upwards of 1000 seconds to complete this. Surely there has to be a faster way of searching for and appending text rather than iteratively for each row in the sheet via a for loop.

function removeDeleted() {
  var ss = SpreadsheetApp.openById(BD_SPREADSHEET_ID);
  var data = ss.getSheetByName(DATA_TAB);
  var dataRange = data.getDataRange().getValues();
  var colData = [];

  for (var i = 1; i < dataRange.length; i++) {
    colData.push(dataRange[i][0]);
  }

  for (var i = 0; i < colData.length; i++) {

    // Take every cell except the first row on col Q (11), as that is the header
    var comments_cell = data.getDataRange().getCell(i + 2, 11).getValue();

    // Check for string "delete" inside cell
    if (comments_cell.toString().indexOf("delete") !== -1 || comments_cell.toString().indexOf("Delete") !== -1) {

      // Check for string "removed" not already inside cell
      if (!(comments_cell.toString().indexOf("removed") !== -1)) {

        // Append ", removed"
          data.getDataRange().getCell(i + 2, 11).setValue(comments_cell + ", removed");
      }
    }
  }
}

I'm still learning google apps script and spreadsheet integration, so I have a feeling there may be some redundancy with the two for loops. Any help would be appreciated.

Answer 1

I haven't tested this but I think this should do what you need. I've commented each line to help explain the logic.

You can write all the data in bulk, like I've done below, or write it in the loop. If you're gonna do a lot of writes/updates then doing in bulk is better.

function removeDeleted()
{
    // get the spreadsheet
    var ss = SpreadsheetApp.openById(BD_SPREADSHEET_ID);

    // get the sheet
    var dataSheet = ss.getSheetByName(DATA_TAB);

    // get the values of the data range as a 2D array
    var data = dataSheet.getDataRange().getValues();

    // first row (index = 0) is header
    // get the index of the column we want to check
    var columnToCheck = data[0].indexOf("COLUMN NAME");

    // track if any data is changed
    var dataChanged = false;

    // go through each row
    // skip the first row since its the header row
    for(var i = 1, numRows = data.length; i < numRows; ++i)
    {
        // get the cell value for the current row and column we want
        var cellValue = data[i][columnToCheck];

        // only if the cell value has delete in it
        if(cellValue.match(/delete/i))
        {
            // only if the cell value does not have removed in it
            if(!cellValue.match(/removed/i))
            {
                // update the value in the 2D array
                data[i][columnToCheck] += ", removed";

                // mark that data changed so we know to write it back to the sheet later
                dataChanged = true;
            }
        }
    }

    // only if data actually changed
    if(dataChanged)
    {
        // write it back to the sheet
        dataSheet.getDataRange().setValues(data);
    }
}