Solved

onOpen. check dates in column, colour background

Posted on 2016-11-24
6
75 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 
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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Learn new improvements released by Google for Google Calendar. Noted in this article are simple tips and tricks that can make your everyday use of Google Calendar better.
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.
This Micro Tutorial demonstrates in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate Google Calendar to monitor updates with top sites, such as Facebook, Google, Twitter, etc. with Marketing News. Each update of Google Calendar can be monitored, correlate dips and spikes in your website traffic, …

717 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