Solved

onOpen. check dates in column, colour background

Posted on 2016-11-24
6
68 Views
Last Modified: 2016-12-24
Hi All,

i'd like an onOpen and onEdit which checks down column  A on one particular sheet which is called dataEntry. Cells which contain a date that is 6+ days old should have their background colour changed to red.

please note i have several other onOpen/onEdit functions so i'm aware they needs unique names and triggers setting up.

i've found a few examples on line but none of them are quite what i'm looking for and unfortunately my very basic knowledge means i'm struggling to edit them.
0
Comment
Question by:bede123
  • 3
  • 3
6 Comments
 
LVL 82

Expert Comment

by:leakim971
ID: 41900898
Just add more events for your sheet :

test
function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("dataEntry");
  var dates = sheet.getRange("A:A").getValues();
  for(var i=0;i<dates.length;i++) {
    var d = dates[i][0];
    var today = new Date();
    d.setHours(0,0,0,0);
    today.setHours(0,0,0,0);
    var diff = (today - d)/(24*60*60*1000);
    var cell = sheet.getRange("A" + (i+1) + ":A" + (i+1));
    if(diff>=6)
      cell.setBackgroundRGB(255,0,0);
    else
      cell.setBackground("white");
  }
}

Open in new window

0
 
LVL 1

Author Comment

by:bede123
ID: 41900940
hi leakim,

this seems to work if i take the these two lines out:
 d.setHours(0,0,0,0);
    today.setHours(0,0,0,0);


but the problem is that it make also all the blank cells red as well.

all red
0
 
LVL 1

Author Comment

by:bede123
ID: 41900950
so my code looks like this:

function checkDate() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("dataEntry");
  var dates = sheet.getRange("A:A").getValues();
  for(var i=0;i<dates.length;i++) {
    var d = dates[i][0];
    var today = new Date();
    //d.setHours(0,0,0,0);
    //today.setHours(0,0,0,0);
    var diff = (today - d)/(24*60*60*1000);
    var cell = sheet.getRange("A" + (i+1) + ":A" + (i+1));
    if(diff>=6)
      cell.setBackgroundRGB(255,0,0);
    else
      cell.setBackground("white");
  }
}

Open in new window



and i have setup two triggers. one for onOpen and one for onEdit.

is that correct?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 82

Accepted Solution

by:
leakim971 earned 500 total points
ID: 41900958
try this :
function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("dataEntry");
  var dates = sheet.getRange("A:A").getValues();
  for(var i=0;i<dates.length;i++) {
    var d = dates[i][0];
    var isNotString = typeof d != "string";
    if(isNotString) {
      var today = new Date();
      d.setHours(0,0,0,0);
      today.setHours(0,0,0,0);
      var diff = (today - d)/(24*60*60*1000);
      var cell = sheet.getRange("A" + (i+1) + ":A" + (i+1));
      if(diff>=6)
        cell.setBackgroundRGB(255,0,0);
      else
        cell.setBackground("white");
    }
  }
}

Open in new window

0
 
LVL 1

Author Closing Comment

by:bede123
ID: 41900962
yep spot on thanks.

did i actually need to setup the triggers for onOpen and onEdit?
0
 
LVL 82

Expert Comment

by:leakim971
ID: 41900965
Yes, set the triggers
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Whether you’re looking to gather data for research or gather feedback on an idea, being able to build and distribute your own online survey is not only cost-effective, but allows you to reach a larger audience and receive results in real-time. Googl…
If your app took Google’s lash recently, here are the 5 most likely reasons.
This Micro Tutorial will demonstrate how to use comments in Google Docs. You will learn how to create, edit, and delete a comment.
Shows how to create a shortcut to site-search Experts Exchange using Google in the Chrome browser. This eliminates the need to type out site:experts-exchange.com whenever you want to search the site. Launch the Search Engine Menu: In chrome, via you…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question