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

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

function paymentTracker() {
  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
  }
 }
}

CodePudding user response：

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

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)

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:

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

CodePudding user response：

Payment Tracker

function paymentTracker() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const rg = sh.getRange(2, 11, sh.getLastRow() - 1, sh.getLastColumn() - 10);
  rg.setFontColor("black")
  const vs = rg.getDisplayValues();
  const d = new Date();
  //Logger.log('y: %s,m: %s,d: %s', d.getFullYear(), d.getMonth(), d.getDate());
  const dt = new Date(d.getFullYear(), d.getMonth(), d.getDate());
  const dtv = dt.valueOf();
  const dt3 = new Date(d.getFullYear(), d.getMonth(), d.getDate()   3);
  const dt3v = dt3.valueOf();
  vs.forEach((r, i) => {
    let ds = r.map(ds => {
      let t = ds.split('/');
      //Logger.log(JSON.stringify(t))
      let v = new Date(Number(t[2]), Number(t[1]) - 1, Number(t[0])).valueOf();
      let diff3 = dt3v - v;
      if (dt3v == v) {
        return "yellow";
      } else if (dtv == v) {
        return "green";
      } else {
        return "pink";
      }
    });
    sh.getRange(i   2, 11, 1, ds.length).setBackgrounds([ds]);
  })
}