highlight - Apps Script getRange.setBackgroundRGB not firing - Stack Overflow

admin管理员组

文章数量:1296237

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.

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.

• google-apps-script
• highlight

Share Improve this question Follow edited Feb 12 at 8:55 Geoff Mulhall asked Feb 11 at 22:02 Geoff MulhallGeoff Mulhall 333 bronze badges 8

• 2 Would you be able to provide your sample sheet, with your initial output, and also your expected output? You may use this to provide a markdown table (you may create one with the help of this link) – Gyul Commented Feb 11 at 22:08
• 1 You could benefit from looking at Logger.log(JSON.stringify(e)). You will find that eventRow is probaby e.range.rowStart – Cooper Commented Feb 11 at 22:09
• 1 We need a minimal reproducible example to assist you. – Cooper Commented Feb 11 at 22:12
• It seems you are using sheet.getRange incorrectly. From the AppsScript docs it seems like you need to pass a string to this function with the A1 notation (e.g. E15) rather you are passing two arguments to this function. – Matthew Lin Commented Feb 12 at 0:30
• 2 If your question is resolved please post the solution as an answer so that other people in the community, who may have the same concern as you, will know that theirs can be resolved. – Jats PPG Commented Feb 12 at 15:36

 |  Show 3 more comments

1 Answer 1

Sorted by: Reset to default 0

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.

本文标签： highlightApps Script getRangesetBackgroundRGB not firingStack Overflow