CODEWITHSUNDEEP
google-apps-scriptspreadsheet
sunny rai
sunny rai

Reputation: 21

I want to set up a day timer that continues to count the days as soon as any data is entered into the row of spreadsheet

Could Someone please help me with the fallowing question?

I set up a Spreadsheet that will relate to the question below https://docs.google.com/spreadsheets/d/1sp8j2zNzzr-lnkMPnYt_aukKM7vqf_31Tq71De_xr04/edit?usp=sharing

(Im not sure the fallowing is possible. Please Advise)

I want to set up a day timer that continues to count the days as soon as any data is entered into the row and for it to stop when I type a word like "X" or even better checkmark in a checkbox. Once I Check mark or type the word, I want the entire row to Greyout/Blackout (highlight with Black)

I am trying to set this sheet up for work and I will truly appreciate any help.

To give more detail, I built a form through google that I will use to jot down who called me, why they called me and if it is an High Priority call or a low priority call, if they require service to there machines etc... Basically this form creates a spreadsheet, I want to be able to complete the task from that call and checkmark it or "something" that will blackout the row letting me know I have completed the task, If however I have not, than I want a timer telling me how long its been since I took that call, 3hrs, 4 days, 10 days, etc... Hope that makes more sense

Thank you

Sunny Rai

Upvotes: 1

Views: 286

Answers (2)

Eric Koleda
Eric Koleda

Reputation: 12671

You can use a simple spreadsheet formula to calculate the number of days difference between the timestamp column and today:

=DATEDIF(A2, NOW(), "D")

There is no built-in way to have this formula automatically added to each new submission, but you can use the copyDown add-on to achieve that.

Upvotes: 0

Jesse Scherer
Jesse Scherer

Reputation: 1522

What you're describing is totally possible!

Step by step, here's what you'll want to do...

  • First, decide which column contains your day counter. In the case of your example, that's column K. Column K is the 11th column, which will be important later.
  • Next, we'll make a function. We'll call it daily().
  • In that function, we will do a few things
    • We get a reference to the "data range" of the sheet -- the area where there is stuff. Call it dataRange
    • Then get an array of all of the values in the sheet: formValues
    • Next get an array of all of the background colors in the sheet: formColors
    • Then we loop through the values (except the header) line by line...
      • If we find a number in column K, we change the number to one higher
      • If we do not find a number in column K, we put a 1
    • While we are looping, we also make some changes to the array of background colors
      • If we found a number greater than or equal to 10, we set the background color for the whole row to black
    • Last, we write back the changed values to the same Spreadsheet Range as we got them...
    • ...and we write back the possibly changed colors to the same range too
  • Once we've written the function, we add a time-based trigger which runs daily() every day

But what does this all look like? Glad you asked! The function looks like this:

function daily() {
  var dataRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getDataRange();
  var formValues = dataRange.getValues();
  var formColors = dataRange.getBackgrounds();
  for (var i = 1; i < formValues.length; i++) { // Start on i=1 to skip the header row
    var dayCount = formValues[i][10]; // The current day count is on column 11, which is index 10 because we zero-index
    if (dayCount == '') { // If dayCount isn't set...
      dayCount = 1;
    } else { // Otherwise, if it is...
      dayCount = dayCount + 1;
    }
    formValues[i][10] = dayCount; // Update the original grid of values
    if (dayCount >= 10) { // If the dayCount is 10...
      for (var j = 0; j < formColors[i].length; j++) { // Loop over the width of the form colors
        formColors[i][j] = '#000000'; //Change whatever background color was there to black
      } //end for j
    } //end if dayCount>=10
  } //end for i
  /* At this point, formValues and formColors contain what should be in the sheet for this row */
  dataRange.setValues(formValues).setBackgrounds(formColors); // Update the range with our new values
}

Remember also that you must make a trigger in Resources > Current Project's Triggers. Set yourself up a trigger to run daily as a time-driven event, configured as a Day timer for whenever you won't be working. Then you're done!

Upvotes: 1

Related Questions