CODEWITHSUNDEEP
google-sheetsgoogle-apps-scriptgoogle-sheets-formulagoogle-sheets-apiclasp
Minseo
Minseo

Reputation: 11

Google Apps Script Spreadsheet conditional format rule builder is applying the rule to a different range than what I wrote

I have a project where I want to save a response from someone and record it to google spreadsheet. The response needs to be color coded if the response is exactly the same with the second row's value.

  var conditionalRange = sheet.getRange("D4:F20");

  var correctRule = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied('=INDIRECT("R" & ROW() & "C" & COLUMN(), FALSE) = INDIRECT("R2C" & COLUMN(), FALSE)')
    .setBackground('#4a86e8')
    .setRanges([conditionalRange])
    .build();

  var rules = sheet.getConditionalFormatRules();

  rules.push(correctRule);
  sheet.setConditionalFormatRules(rules);

As you can see, I have specified a range of D4:F20 to be the range the format should be applied to. However after the execution, the rule shows as thus: Apply to Range = D1:G2 It is now being applied to D1:G2

I am now confused because I thought setRanges meant apply this formula to this range, but perhaps I understood it wrong?

edit: okay I just found out that pushing with clasp will sometimes result in codes not being pushed... Perhaps pushing too much causes a delay in updating the code?

Upvotes: 1

Views: 180

Answers (1)

ZachTheDev
ZachTheDev

Reputation: 96

Try deleting the conditional formatting rule in Google Sheets and rerunning the function. If it comes back in the right range, the below might help you. If it comes back but in the wrong range, then it may have something to do with Clasp as you mentioned, but I do not have any knowledge of Clasp to assist you.

If the same formula is listed and it already has a range, the range is not updated. When I create an Apps Script that uses formatting builders, I typically like to clear all formats and build them all again using specific functions.

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
sheet.clearConditionalFormatRules();

Upvotes: 1

Related Questions