无法从谷歌表单中填写谷歌表格的日期 [英] Cannot get date from google sheet filled by google forms

问题描述

目的：从表格中提取日期和时间（由填充的Google表单创建），并在电子邮件正文中使用它们自动使用脚本进行设置。
脚本编辑器代码：

 函数sendEmails（）{
 var sheet = SpreadsheetApp.getActiveSheet（）; 
 var startRow = 16; //处理
的第一行数据var numRows = 1; //（$ var $ = startRow; j< startRow + numRows; j ++）
 {
 var color = sheet.getRange（j，10）//处理
 
的行数.getBackground（）; 
 if（＃00ff00== color）
 {
 //获取单元格范围A2：B3 
 var row = sheet.getSheetValues（j，1,1， 14）; //提取第j行到第14行/第col col 
 var nameStudent = row [0] [0] ++ row [0] [1]; 
 var time = row [0] [6] +on; // + row [0] [7] +at+ row [0] [8]; 
 var nameRecipient =Dear+ row [0] [9] +，; 
 var emailRecipient = row [0] [13]; 
 var subject =日期和时间+ row [0] [5] +at+ row [0] [6]; 
 
 var message = nameRecipient + sheet.getRange（L11）。getValue（）+ nameStudent + sheet.getRange（M11）。getValue（）+ time + nameStudent + sheet.getRange（ N11\" ）的getValue（）; 
 
 MailApp.sendEmail（emailRecipient，subject，message）; 
 sheet.getRange（j，10）.setBackground（＃ff9900）; 
 
 
 $ b code 
 $ b 
在输出（即电子邮件） ：
 
 
 
日期由填写Google表单的人员输入。 
我正在访问使用表单创建的谷歌表格。工作表的屏幕截图：
  https：//驱动器。 google.com/file/d/0B2hiq6P5ceD-aUZQTDBVUXJYbEk/edit?usp=sharing  
单元格（双击时）会打开上面链接屏幕截图中显示的日历。 
我使用以下代码（使用Google Apps脚本）从表中获取一行： 
 
 
  var row = sheet.getSheetValues（j，1,1，14）; //提取第j行直到14 / Nth col 
  
在调试器中，日期如下所示：  pre> 
 
 
在上面的语句中，数字41855是日期。它实际上是8/4/2014。但我看不懂。我也无法读到上述debug o / p中的时间为0.5277777777810115。
 
 
 
我附上了调试屏幕的屏幕截图：
 
 
 
  https：//驱动器.google.com / file / d / 0B2hiq6P5ceD-aFlDd2g4SlM5MW8 / edit？usp = sharing  
解决方案
假设你的时间提交在电子表格中的第14行。
 $ b $ p将日期和时间值转换为Javascript日期对象，然后从表单中更新日期对象
 
 
 函数sendEmails（）{
 var sheet = SpreadsheetApp.getActiveSheet（）; 
 var startRow = 16; //处理
的第一行数据var numRows = 1; //（$ var $ = startRow; j< startRow + numRows; j ++）
 {
 var color = sheet.getRange（j，10）//处理
 
的行数.getBackground（）; 
 if（＃00ff00== color）
 {
 //获取单元格范围A2：B3 
 var row = sheet.getSheetValues（j，1,1， 14）; //提取第j行到第14行/第col col 
 var nameStudent = row [0] [0] ++ row [0] [1]; 
 var time = row [0] [6] +on; // + row [0] [7] +at+ row [0] [8]; 
 var nameRecipient =Dear+ row [0] [9] +，; 
 var emailRecipient = row [0] [13]; 
 
 //创建日期时间对象
 var dateInput = new Date（row [0] [5]）; 
 var timeInput = new Date（row [0] [6]）; 
 dateInput.setHours（timeInput.getHours（））
 dateInput.setMinutes（timeInput.getMinutes（））; 
 //将日期/时间对象格式化为字符串
 var dateString = Utilities.formatDate（dateInput，Session.getScriptTimeZone（），MM / dd / yyyy hh：mm a）; 
 
 var subject =Date and time+ dateString 
 
 var message = nameRecipient + sheet.getRange（L11）。getValue（）+ nameStudent + sheet.getRange（ M11）的getValue（）+时间+ nameStudent + sheet.getRange（ N11）的getValue（）。; 
 
 MailApp.sendEmail（emailRecipient，subject，message）; 
 sheet.getRange（j，10）.setBackground（＃ff9900）; 
} 
  
} 
} 
 
Purpose: To extract date and time from a sheet (created by filled google forms) and use them in the body of email to be set automatically using the script.
Script editor code:
function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 16;  // First row of data to process
  var numRows = 1;   // Number of rows to process

  for( var j=startRow; j<startRow + numRows; j++)
  {
    var color = sheet.getRange(j, 10).getBackground();
    if("#00ff00"==color)
    {
      // Fetch the range of cells A2:B3
      var row = sheet.getSheetValues(j, 1, 1, 14); //extract j th row till 14/Nth col
      var nameStudent = row[0][0] +" "+row[0][1];
      var time = row[0][6]+" on ";// +row[0][7]+" at "+row[0][8];
      var nameRecipient="Dear "+row[0][9]+",";
      var emailRecipient=row[0][13];
      var subject= "Date and time  "+row[0][5]+" at "+row[0][6];

      var message=nameRecipient+sheet.getRange("L11").getValue()+nameStudent+sheet.getRange("M11").getValue()+time+nameStudent+sheet.getRange("N11").getValue();

      MailApp.sendEmail(emailRecipient, subject, message);
      sheet.getRange(j,10).setBackground("#ff9900");
    }
  }
}
In output (i.e. email):

The date is entered by person filling google form.
I'm accessing the google sheet created using the forms. Screenshot of the sheet:
https://drive.google.com/file/d/0B2hiq6P5ceD-aUZQTDBVUXJYbEk/edit?usp=sharing
The cell (when double clicked) opens a calender shown in the above linked screenshot.
I use the following code (Using Google Apps Script) to get a row from the sheet:
var row = sheet.getSheetValues(j, 1, 1, 14); //extract j th row till 14/Nth col
In the debugger the date looks like:
[["Gaaav", "Budapest","[e-mail]", 9823527827, "San Francisco International Airport(SFO)", 41855, 0.5277777777810115,"Si016", "Al02", "Yaar", "pr", "3112", 94984802569, "[e-mail]"]]
In the above statement the number 41855 is the date. It actually is 8/4/2014 . But I can't read it . Neither can I read the time, which in the above debug o/p, is 0.5277777777810115.

I'm attaching a screenshot of the debug screen:

https://drive.google.com/file/d/0B2hiq6P5ceD-aFlDd2g4SlM5MW8/edit?usp=sharing
解决方案
Assuming your time submission is row 14 in your spreadsheet.

convert both the date and time values in to Javascript date objects, then update the date object from the form with the hours and minutes from the time object from the form.
 function sendEmails() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var startRow = 16;  // First row of data to process
   var numRows = 1;   // Number of rows to process

   for( var j=startRow; j<startRow + numRows; j++)
   {
     var color = sheet.getRange(j, 10).getBackground();
     if("#00ff00"==color)
     {
       // Fetch the range of cells A2:B3
       var row = sheet.getSheetValues(j, 1, 1, 14); //extract j th row till 14/Nth col
       var nameStudent = row[0][0] +" "+row[0][1];
       var time = row[0][6]+" on ";// +row[0][7]+" at "+row[0][8];
       var nameRecipient="Dear "+row[0][9]+",";
       var emailRecipient=row[0][13];

       // build out the date time object
        var dateInput = new Date(row[0][5]);
       var timeInput = new Date(row[0][6]);
       dateInput.setHours(timeInput.getHours())
       dateInput.setMinutes(timeInput.getMinutes());
       // format the date/time object into a string
       var dateString = Utilities.formatDate(dateInput, Session.getScriptTimeZone(), "MM/dd/yyyy hh:mm a");

  var subject= "Date and time  "+ dateString

  var message=nameRecipient+sheet.getRange("L11").getValue()+nameStudent+sheet.getRange("M11").getValue()+time+nameStudent+sheet.getRange("N11").getValue();

  MailApp.sendEmail(emailRecipient, subject, message);
  sheet.getRange(j,10).setBackground("#ff9900");
}
}
}

                        
这篇关于无法从谷歌表单中填写谷歌表格的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！