Google表格的倒数时钟 [英] Countdown Clock for Google Sheets

问题描述

我想根据输入的时间信息和将来的30天进行简单的倒计时...单元格"Dat"将输入时间信息，而单元格"Date"将具有30天.单元格D的倒计时，现在，我已经能够注册输入的时间以及将来的30天，每次在剩余时间"单元格中运行该命令时，我都会收到一个错误消息.救救我！

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!

预先感谢

马丁

推荐答案

您可能只需要在示例表的Time Left列(D3)中使用公式即可即可.

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

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

，然后使用fill句柄.小方块位于所选单元格的右下角-用公式填充该列.这将为您提供Dat + 30 days和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()

这篇关于Google表格的倒数时钟的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！