Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

onOpen. check dates in column, colour background

Posted on 2016-11-24
6
Medium Priority
?
97 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 83

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 83

Accepted Solution

by:
leakim971 earned 2000 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 83

Expert Comment

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Google is more than just a search engine. Over the years the company has developed a wide range of online services that are readily available to all users. This article highlights how one can use Google services for simple project management.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
This Micro Tutorial will demonstrate using Google Doc how to import live data to another spreadsheet in Google Spreadsheets using the IMPORTRANGE function.
This Micro Tutorial will demonstrate common damaging and frequent mistakes I see in most analytic audits. Most of them are campaign tagging mistakes, so this video will break it down into simple steps.
Suggested Courses
Course of the Month12 days, 4 hours left to enroll

564 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