CODEWITHSUNDEEP
google-apps-scripthighlight
Geoff Mulhall
Geoff Mulhall

Reputation: 3

Apps Script getRange.setBackgroundRGB not firing

In response to google form submission I have a script triggered to do some data checking, and highlight two possible data issues in the appropriate cells of the sheet attached to the form, as well as send an email to the user submitting the form.

Everything is working fine except for highlighting the cells in the sheet with the issues. Below are the two relevant sections of code

Section 1

var responseSheet = e.source.getSheetByName('Form Responses 1');

if (respRating != recordRating){
          Logger.log('shade Rating');
          Logger.log(eventRow);
          Logger.log(responseArr[0].indexOf('Rating'));
          responseSheet.getRange(eventRow,responseArr[0].indexOf('Rating') + 1).setBackgroundRGB(177,156,217);  //Highlight the problem
          Logger.log('shade Rating');

Section 2

else {
          Logger.log('shade Pension No');
          Logger.log(eventRow);
          Logger.log(responseArr[0].indexOf('Pension Card Number'));
          responseSheet.getRange(eventRow,responseArr[0].indexOf('Pension Card Number') + 1).setBackgroundRGB(177,156,217);  // Highlight the problem
          Logger.log('shade Pension No');
    }

Now from the logging it's clear both sections of code are being executed and all the fields for getRange and setBackground methods are OK.

Here are the Cloud logs

Feb 12, 2025, 8:37:31 AM    Info    shade Rating

Feb 12, 2025, 8:37:31 AM    Info    38.0

Feb 12, 2025, 8:37:31 AM    Info    8.0

Feb 12, 2025, 8:37:31 AM    Info    shade Rating


Feb 12, 2025, 8:37:31 AM    Info    shade Pension No

Feb 12, 2025, 8:37:31 AM    Info    38.0

Feb 12, 2025, 8:37:31 AM    Info    7.0

Feb 12, 2025, 8:37:31 AM    Info    shade Pension No

All I can think is that responseSheet is no longer available BUT I have later code which does highlight an entire row properly?

Any help appreciated.

I was expecting the highlighting would show in the responseSheet but it does not. I've run out of ideas on how to debug this.

Upvotes: 0

Views: 82

Answers (1)

Jats PPG
Jats PPG

Reputation: 790

Posting the Comment as an Answer

As per the original poster, @Geoff Mulhall, watching the spreadsheet while the code ran revealed the background color flashing on and off. This helped them find the problematic line of code and realize the issue was with their own logic.

Note: Posting this as a Community Wiki so others in the community will know that the posted code itself isn't wrong, but the issue lies with its own logic.

Upvotes: 0

Related Questions