CODEWITHSUNDEEP
dategoogle-apps-scriptgoogle-sheetsformatting
Jumba Mark
Jumba Mark

Reputation: 35

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
  }
 }
}

Upvotes: 0

Views: 92

Answers (2)

Cooper
Cooper

Reputation: 64032

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 "purple";
      } else if (dtv == v) {
        return "green";
      } else {
        return "pink";
      }
    });
    sh.getRange(i + 2, 11, 1, ds.length).setBackgrounds([ds]);
  })
}

Upvotes: 2

Mart&#237;n
Mart&#237;n

Reputation: 10084

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

Upvotes: 2

Related Questions