0

My goal is for an end-user to paste the timestamp from the form into a dialoge box, and it will get the results of the response that has that particular timestamp. The script I am working on is bound to the form.

I can get a result from a timestamp, however in building the function, I found out that the response.getTimestamp(), will produce a different result than the timestamp in the form.

I am wonding if anyone has an idea for a workaround, where someone can just paste the timestamp from the sheet and thet the corresponding result in the form script.

Here is my code which I thought should work, but doesn't

function getCorrectTimestamp(t){
 t = "12/11/2015 10:28:50";
// t = "Fri Dec 11 2015 10:28:49 GMT-0500 (EST)"; 
// t = "Fri Dec 11 2015 10:29:28 GMT-0500 (EST)"; 
// t = "Fri Dec 11 2015 10:29:12 GMT-0500 (EST)";  
 var n = 0;
 var td = new Date(t); 
 var form = FormApp.getActiveForm(); 
 var r = form.getResponses(td);
 var rt = r[n].getTimestamp();
 var name = r[n].getItemResponses()[0].getResponse(); 

  Logger.log(t);

}
Bjorn Behrendt
  • 1,204
  • 17
  • 35
  • 2
    Could you clarify "end-user"? Is that the person who owns the form, or are you referring to respondents? Also, when you say they are getting the timestamp from "the form", do you mean "spreadsheet"? (How can they get a timestamp from a form?) – Mogsdad Dec 12 '15 at 04:25
  • end-user is the person who owns the form, and they would be getting the timestamp from the sheet. The problem is that sheet timestamp does not match the the one you get by using .getTimestamp(); through App Script. – Bjorn Behrendt Dec 12 '15 at 23:47
  • I just came across this in a new question posted. When you get a Timestamp from a spreadsheet it is casting it to a date object instead of reading it as a string. So the SS value is "12/11/2015 10:28:50" if your do a range.getValue() it comes back as a Date object and casts to "Fri Dec 11 2015 10:28:49 GMT-0500 (EST)" – Spencer Easton Dec 14 '15 at 15:10
  • Thank you so much Spencer, I have been building a very crazy workaround that was attempting to find the closest match. It still sucks that the text doesn't exactly match, but at least I can now move forward with a better work around. – Bjorn Behrendt Dec 15 '15 at 02:02

2 Answers2

0

There is still no direct link from the sheet to the form results, but the below code seems to be reliable enough.

function sendOtherResponse(ts){

//  ts = "12/14/2015 11:12:59";
//  ts = "12/14/2015 11:13:05";
//  ts = "12/14/2015 11:13:10";
  ts = "12/14/2015 11:13:16";

 // get actual date from paste 
 var form = FormApp.getActiveForm();
 var destId = form.getDestinationId();
 var sheet = SpreadsheetApp.openById(destId);
 var data = sheet.getDataRange().getValues();
 var dataD = sheet.getDataRange().getDisplayValues(); 
 var date = ""; 

  for(i in dataD){
   var tsDataD = dataD[i][0];
    if (tsDataD == ts){
     date = data[i][0];
     break;
    } 
  }
  var thisResponse = form.getResponses(date)[0];
  var test = thisResponse.getItemResponses()[0].getResponse();
  Logger.log(test);
}
Bjorn Behrendt
  • 1,204
  • 17
  • 35
0

I know this is incredibly old but I thought I would contribute for anyone needing to correlate a timestamp from a response Sheet to the same Form response timestamp. See this snippet of my code:

var timestamp = formResponses[0].getTimestamp().toString();
var sstimestamp = ss.getRange('A2').getValue().toString();

if (timestamp == sstimestamp){
  flag3 = 1;
}


Logger.log('Form Time stamp: ' +timestamp +'   '+typeof timestamp)
Logger.log('Sheet Time stamp: ' +sstimestamp +'   '+typeof sstimestamp)
Logger.log('Flag for equal time stamps detected:  ' +flag3)

Here is the logger output:

11:10:25 AM Info Form Time stamp: Thu Jul 02 2020 13:59:24 GMT+0200 (South Africa Standard Time) string

11:10:25 AM Info Sheet Time stamp: Thu Jul 02 2020 13:59:24 GMT+0200 (South Africa Standard Time) string

11:10:25 AM Info Flag for equal time stamps detected: 1

Cell A2 in my response Sheet obviously contains the time stamp that corresponds with the first Form response timestamp. Note that what I actually see in A2 on the Sheet is: 02/07/2020 13:59:25

So you need to fetch the data from the sheet using code in order to get it into the correct format to compare to the Form. Also note that when you get the value, it is recognized as type object and then comparing them doesn't work. That is why I had to use .toString() to convert it to a string first and then comparing them works.

Gary
  • 13
  • 4