Solved

onOpen. check dates in column, colour background

Posted on 2016-11-24
6
63 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Generate Numbers in JQuery file 11 66
Html CheckBox obtain Its Value 5 27
jQuery on Submit 4 36
Do alert on select 6 13
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…
Boost your ability to deliver ambitious and competitive web apps by choosing the right JavaScript framework to best suit your project’s needs.
This Micro Tutorial demonstrates in Google Analytics how to create a custom report that shows you traffic over time using the month of year dimensions. There are also instructions on how to fix Google's odd month of year formatting, which Microsoft …
This Micro Tutorial will demonstrate the easy use of Gmail embedding images in your email so the recipient of your email can view them in context.

789 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