Script to format a range of cells and insert a spe

Question

I have a planner type Google spreadsheet where data added daily by 8-10 users. When I add a date to a cell, I want all the cells in the same row after that date to be formatted and added a text value something like "ENDED".

At the moment I am doing it with conditional formatting and with an ArrayFormula to add the text value. The problem is that for the ArrayFormula to work the cells must be empty and in my sheet the cells they might contain data before the "ENDED" date cell.

Is there a way to do this with a script?.... and if the script can handle also the formatting of the cells that will be the best solution.

Here is my sample file to understand better what I am trying to do...

https://docs.google.com/spreadsheets/d/1QplyEcNu-svYwFq9wvPVEKnsEP1AnrlAkbBxNwEFPXg/edit#gid=2087617521

Answer 1

You can do this with a trigger and a custom function.

Create a new apps script project and use this code:

function onEdit(e) {
  if (e.range.getColumn() ==2) {
    //User edited the date column
    if (typeof e.range.getValue() === typeof new Date()) {
      //Value of edit was a date
      endColumns(e.range.getRow(), e.range.getValue());
    } else if (e.range.getValue() === ""  || e.range.getValue() === null) {
      var sheets = SpreadsheetApp.getActiveSheet();
      var resetRange = sheets.getRange(e.range.getRow(), e.range.getColumn()+1, 1, sheets.getMaxColumns()-e.range.getColumn());
      resetRange.clear(); //Will delete all text, not only the "ENDED" text.
    }
  }
}

function endColumns(rowNum, limitDate) {
  var sheets = SpreadsheetApp.getActiveSheet();

  var colOffset = 3; //Offset to account for your row Headers
  var dateHeader = sheets.getRange(1, colOffset, 1, sheets.getMaxColumns()-colOffset);

  var availableDates = dateHeader.getValues()[0];

  var foundCol = 0;
  for (var i=0; i<availableDates.length; i++) {
    if (availableDates[i]>=limitDate) {
      break;
    }
    foundCol++;
  }

  var rewriteCells = sheets.getRange(rowNum, foundCol+colOffset, 1, sheets.getMaxColumns()-(foundCol+colOffset));

  //Add your formatting and text below:
  rewriteCells.setValue("Ended");
  rewriteCells.setBackground("red");
  rewriteCells.setFontColor("yellow");

  //Clear all cells that are "white" (no header)
  for (var i=0; i<availableDates.length; i++) {
    if (availableDates[i]==="" || availableDates[i] ===null) {
      sheets.getRange(rowNum, colOffset+i).clear();
    }
  }
}

Then, create a trigger to run the onEdit function on every edit.

In this case there are some hardcoded values:

• e.range.getColumn() == 2 for the row where you add the dates on
• var colOffset = 3 for the number of columns to skip before reading the dates

Hope this helps!