Search code examples
dategoogle-apps-scriptgoogle-sheetsformatting

Getting the cell given the cell value in google sheets using app script

I'm trying to write a script that tracks payment dates in google sheets (shows a different colour (either FontColor or Background) three days before payment, another colour on the day of payment and a totally different colour after the payment date.I'd appreciate if there's anyone with know how on how to use those values to get the cell name and use it to change the FontColor or alternatively if there's a better solution Here is my google sheet [![enter image description here][1]][1]

This is the code I've written to get the dates into a list

function myFunction() {
  let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  let lastRow = spreadsheet.getLastRow();
  let lastCol = spreadsheet.getLastColumn();

  var dataRange = spreadsheet.getActiveSheet().getRange(2, 11, lastRow, lastCol)
  dataRange.setFontColor("green")
  var data = dataRange.getDisplayValues();
  let dates=[];

  for (let i=0; i < dates.length; i++ ) {
  // console.log(dates[i])
  if (dates[i] === new Date().toLocaleDateString()) {
    dataRange.setBackground('pink')
  } else if (dates[i]) {
    // do sth
  } else {
    // maintain the current state
  }
 }
}
Solution

  • Does it need to be with scripts?? With conditional formatting that would be MUCH faster, easier and uploads constantly. enter image description here

    You can apply it to the entire sheet or to a specific range. Use this custom formula (change A1 with the top left formula of your range) =if(A1="",FALSE,(A1 - Today()) < 0) enter image description here

    Get sure to set these conditions in the correct order (in these case it would be preferrable to be the past dates, the actual date and the close future dates). Like this:

    enter image description here Here you have a link to play with: https://docs.google.com/spreadsheets/d/1zhEFRQwOyAYQwXfv5lYTjI7B-6fIfz1rgdCt3MGvzmI/edit?usp=sharing