CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
avs
avs

Reputation: 678

Color rows based on date in merged cell in first column in Google Sheet script

I have a Google Sheet sort of like below, dates in the first column in a merged cell spanning a few rows. I am trying to make a script that automatically colors the rows (or the text) when the date in the first column is equal to the date the script is run. In the example screenshot, when the date is October 1st, rows 11-15 should be colored. Since the script also ran the day before, rows 6-10 are already colored at this point. This is probably possible using conditional formatting, but I also want to apply other functions to these cells (such as locking them from editing) so I need to do it in a script.

I found several similar questions, such as this one, but I was not able to adapt any of them to my spreadsheet. Maybe the merged cell is causing problems?

enter image description here

Upvotes: 0

Views: 336

Answers (1)

Marios
Marios

Reputation: 27348

Explanation:

  • The following script will get the date value for the first cell of every merged range in column A of Sheet1.
  • It will compare that date value with the date of today.
  • If there is a match between the cell date and the date today, then it will color the rows (until column F) with orange. Otherwise, it will color them with white color.

Solution:

function myFunction() {
  
  const today = (new Date()).toLocaleString("sv-SE").slice(0,10);
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getSheetByName("Sheet1");

  const range = sh.getRange("A1:A"+sh.getLastRow());

  const mergedRanges = range.getMergedRanges();
  
  mergedRanges.forEach(mR=> 
     {                  
     temp_vals = mR.getValues().flat();
     cell_date = (new Date(temp_vals[0]).toLocaleString("sv-SE").slice(0,10));
  
     if(today === cell_date) {
       sh.getRange(mR.getRow(),mR.getColumn(),mR.getHeight(),6).setBackground('#FF8C00');
     }   
  else{
       sh.getRange(mR.getRow(),mR.getColumn(),mR.getHeight(),6).setBackground('#ffffff');   
  }
     });
}

References:

Upvotes: 1

Related Questions