Countdown Clock for Google Sheets

Question

I would like to make a simple count down based on the time information was input and 30 days in the future ... Cell 'Dat" would have the time information was inputted and Cell "Date" would have 30 Days in the future with cell D having the count down. For now I have been able to register the time inputted and 30 days in the future. I get an error every time I run this in the cell "Time left" Please let me know if you can help me!

function onEdit(event)
{ 
var timezone = "GMT-04:00";
var timestamp_format = "M/dd/yyyy"; // Timestamp Format.
var updateColName = "Name";
var updateColName2 = "Address";
var updateColName3 = "Address";
var timeStampColName = "Date";
var timeStampColName2 = "Dat";
var timeStampColName3 = "Date";
var timeStampColName4 = "Time";
var updateColName4 = "Address";

var timeStampColName5 = "Time Left";
var updateColName5 = "Address";

var sheet2 = event.source.getSheetByName('Sheet1');


var actRng = event.source.getActiveRange();
var editColumn = actRng.getColumn();
var index = actRng.getRowIndex();


var headers2 = sheet2.getRange(1, 1, 1, 
sheet2.getLastColumn()).getValues();
var headers4 = sheet2.getRange(1, 1, 5, 
sheet2.getLastColumn()).getValues();
var headers5 = sheet2.getRange(1, 1, 4, 
sheet2.getLastColumn()).getValues();



var dateCol2 = headers2[0].indexOf(timeStampColName2);  
var dateCol4 = headers4[0].indexOf(timeStampColName4);
var dateCol5 = headers5[0].indexOf(timeStampColName5);


var updateCol2 = headers2[0].indexOf(updateColName2); updateCol2 = 
updateCol2+1; 
var updateCol4 = headers4[0].indexOf(updateColName4); updateCol4 = 
updateCol4+1;
var updateCol5 = headers5[0].indexOf(updateColName5); updateCol5 = 
updateCol5+1;


if (dateCol2 > -1 && index > 1 && editColumn == updateCol2)
{  var cell2 = sheet2.getRange(index, dateCol2 + 1);
  var date2 = Utilities.formatDate(new Date(), timezone, 
timestamp_format);
cell2.setValue(date2);
 }


  if (dateCol4 > -1 && index > 1 && editColumn == updateCol4)
 { var cell4 = sheet2.getRange(index, dateCol4 + 1);

 var date =  new Date();
 var secondDate = new Date(date.setDate(date.getDate()+ 30));

 cell4.setValue(secondDate);
 Logger.log(secondDate);


 }
 if (dateCol5 > -1 && index > 1 && editColumn == updateCol5)
 { var cell5 = sheet2.getRange(index, dateCol5 + 1);

 var future = Utilities.formatDate(new 
 Date(date.setDate(date.getDate()+ 30)), timezone, timestamp_format);
 //var future = new Date(); // get NOW
  //future.setDate(future.getDate() + 30); //Add the specified days to 
 the current time



 cell5.setFormula( '=' + secondDate + '- now()');

Here is a sample link where I tried to run the code.

https://docs.google.com/spreadsheets/d/1brkXxkSut3xUVK2ouUsUaMyle-EnRV4dzxWVAaXIabg/edit?usp=sharing

Let me know if you need anything else I may have missed!

Thanks in advance,

Martin

Answer 1

You could probably just use a formula in the Time Left column (D3) of your example sheet

=DAYS(DATEVALUE(A3+30),NOW())

and then use the fill handle. The little square are the bottom right of the selected cell - to fill in that column with the formula. This will give you the number of days left between Dat + 30 days and NOW()