0

I am trying to figure out why my script is not working: I have a sheet called "active unentered invoices" where my master data is housed, I am using a true/ false statement is used in column V and manual data is in column P of the same sheet. I would like the data validation list in column P to restore itself to blank so it returns to my staffs workflow. The script I am using is below which is working but is skipping random cells that should have been cleared in column P (90 out of 2000 were skipped);

function ClearStatusColumn() {
  var s = SpreadsheetApp.getActive().getSheetByName('Active UNENTERED Invoices');
  s.getRange('V2:V' + s.getLastRow())
    .getValues()
    .forEach(function (r, i) {
      if (r[0] == 'TRUE') {
        s.getRangeList(["P" + (i + 1)]).clearContent();
      
      }
    });
}

BTW, I had looked at Clear cell based on modification of another cell and was not able to make it work for my purposes.

3
  • what is happening when your code runs? Are you logging errors in the console? Feb 21 at 21:15
  • Welcome to Web Applications Stack Exchange. e.range.getSheetByName() will throw an error because a Range object does not have that method. Check My Executions for failed executions, and view those logs to learn more. To fix the code, refer to the resources listed at Apps Script at Stack Overflow. Feb 21 at 21:22
  • To be perfectly honest nothing happens. I did play with it a while and decided to change it entirely. My new one is kind of working BUT for some reason it is skipping random cells that should have been cleared. This is the new script that I came up with: function ClearStatusColumn() { var s = SpreadsheetApp.getActive().getSheetByName('Active UNENTERED Invoices'); s.getRange('V2:V' + s.getLastRow()) .getValues() .forEach(function (r, i) { if (r[0] == 'TRUE') { s.getRangeList(["P" + (i + 1)]).clearContent(); } }); } Feb 23 at 4:50

2 Answers 2

0

Chances are that the data contains a mix of text strings like "TRUE" or "true" and the Boolean value true.

Spreadsheet formulas will consider those text strings equal to the Boolean true in many contexts, but JavaScript will consider them different.

Letter case and spacing also matters. "TRUE" !== "true" and "TRUE" !== " TRUE".

One way to make it work is to coerce the values to lower case text strings where white space has been trimmed, like this:

function clearStatusWhenColumnIsTrue() {
  const sheet = SpreadsheetApp.getActive().getSheetByName('Active UNENTERED Invoices');
  const targetRange = sheet.getRange('S2:S');
  const targetValues = targetRange.getValues();
  const triggerValues = sheet.getRange('V2:V').getValues().flat();
  const newValues = targetValues.map((row, index) =>
    'true' === String(triggerValues[index]).toLowerCase().trim()
      ? [null]
      : row
  );
  targetRange.setValues(newValues);
}
-1

Thank you for all of your help. I was able to get it working, below was my solution.

function ClearStatusColumn() {
  var s = SpreadsheetApp.getActive().getSheetByName('Active UNENTERED Invoices');
  s.getRange('V2:V' + s.getLastRow())
    .getValues()
    .forEach(function (r, i) {
      if (r[0] == 'TRUE') {
        s.getRange(["P" + (i + 2)]).clearContent();
      
      }
    });
}
1
  • Welcome to Web Applications. From How to Answer "Brevity is acceptable, but fuller explanations are better." Please explain what changes you did to the code.
    – Rubén
    Feb 25 at 21:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.