CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-macros
StackUser
StackUser

Reputation: 5398

Google app scripts: Assign zero for empty cells

I am new to Google Apps Script, trying to set values to a column based on its current value and a flag.

  1. If Flag = Y then floor the value in C1:

    C1 column value =23.9895
    Expected value=23

  2. If Flag = N then round the existing value in C1:

    C1 column value =23.9895
    Expected value=24

  3. If the flag is either Y or N then write 0:

    C1 column value=empty cell
    Expected Value=0

I already implemented the below code. It was working partially. The first two scenarios works fine but the third scenario fails.
When I try to set zero, I am getting #NUM! error instead of zero. Not sure how to do it.

...
do {
  sRange = "Q" + iCt;
  if ((gDecimalInPrice == "Y") && (!isNaN(sheet.getRange(sRange).getValue()))) {
    sheet.getRange(sRange).setValue(Math.abs(parseInt(sheet.getRange(sRange).getValue())));
  } else if ((gDecimalInPrice == "N") && (!isNaN(sheet.getRange(sRange).getValue()))) {
    sheet.getRange(sRange).setValue(Math.abs(Math.round(sheet.getRange(sRange).getValue())));
  } else {
    sheet.getRange(sRange).setValue(sheet.getRange(sRange).getValue());
  }
  iCt = iCt + 1;
} while (iCt <= gRowCt);

Upvotes: 2

Views: 2173

Answers (1)

tehhowch
tehhowch

Reputation: 9872

It's much faster to do this via batch operations (and follows official best practices). These read the values into a "2D" JavaScript array (an array of arrays of values), and then you can do all your logic in memory, rather than repeatedly requesting data from the slow Spreadsheet interface.

function foo() {
  const wb = SpreadsheetApp.getActive();
  const sheet = wb.getSheetByName("the sheet name");
  if (!sheet) throw new Error("Sheet with that name is missing");

  const lastRow = sheet.getLastRow();
  const flags = sheet.getRange("A1:A" + lastRow).getValues();
  const valueRange = sheet.getRange("Q1:Q" + lastRow);

  const newValues = valueRange.getValues().map(function (row, i) {
    return row.map(function (value) {
      var flag = flags[i][0];
      if (!flag || (value && isNaN(value))) // No "Y" or "N", or value is non-nullstring non-number, so return value as-is
        return value;
      else if (flag === "Y")
        return value ? Math.floor(parseFloat(value)) : 0;
      else if (flag === "N")
        return value ? Math.round(parseFloat(value)) : 0;
      else // Unknown flag value
        return value;
    });
  });
  // Write all processed values at once
  valueRange.setValues(newValues);
}

As always, you should monitor macro and triggered functions for errors by reviewing your Stackdriver logs (accessible via the Script Editor's "View" menu).

Upvotes: 2

Related Questions