Google Form asks for duration, how to calculate with this in Google App Script

Question

I use stackoverflow on a regular basis to find answers to my problems, but I cannot seem to solve this one. So here is my first question:

I have a Google form that (amongst other things) asks for the duration of a job. I want the google spreadsheet to contain the form-answers, and add some columns. In this case, I want to add the cost of the job, using an hour rate of 126,-

But I keep running into problems in calculating with the duration: my script either tells me its a text (if I use getDisplayValue in retrieving the data), or it gives me a #NUM error in the spreadsheet itself.

Can anyone pinpoint me towards a solution how to retrieve the hours and the minutes from the form-field (time as duration), so I can do some basic math with it in the script?

I've setup a small form and connected spreadsheet showing my problems. The example form only asks for the duration, and places this in the spreadsheet in column 2. In the spreadsheet I've setup a script that runs on form submit an I try to explain all steps I do. The script should take the form input, convert it to hours (as a num-field) and multiply that with the PricePerHour. The result should be placed in column 3 on the same row of the form submit.

This is my script so far:

// CALCULATE COST OF JOB
function calculatePriceDuration(e) {

  // get source data
  var sourceSheet          = SpreadsheetApp.getActiveSheet();                                            // connect to source sheet
  var sourceRow            = sourceSheet.getActiveRange().getRow();                                      // connect to event row (form submit)


  // get destination data
  var destinationSheet    = sourceSheet;                                                                 // connect to destination sheet
  var destinationRow      = sourceRow;                                                                   // connect to destination row
  var destinationColID    = 3;                                                                           // set column number of value to paste


  // set variables
  var colID_FormDuration   = 2;                                                                          // set column number where the form places the duration
  var formDuration         = sourceSheet.getRange(sourceRow, colID_FormDuration).getDisplayValue();      // get value for duration 


  // set price per hour 
  var PricePerHour         = 126;

  // calculate job price
  var PriceForJob          = formDuration * PricePerHour;

  // set destination cell
  destinationSheet.getRange(destinationRow,destinationColID).setValue(PriceForJob);                      // paste value in the 3rd column of the same row of form submit

}

the spreadsheet itself can be found here:

the form can be found here:

Any help is much appreciated! Kind regards, Rob

if you are calling this function from form submit, you can just access the event object `e`: [script in form](https://developers.google.com/apps-script/guides/triggers/events#form-submit_3), [script in response sheet](https://developers.google.com/apps-script/guides/triggers/events#form-submit). This will let you avoid the (erroneous) assumption that the current last row is the row with the data from the submission. — tehhowch, Mar 16 '18 at 15:36
Also, [`getDisplayValue()`](https://developers.google.com/apps-script/reference/spreadsheet/range#getDisplayValue()) only gets a text string - you want the actual value, given by [`getValue()/getValues()`](https://developers.google.com/apps-script/reference/spreadsheet/range#getValue()). The result will then properly be a workable Javascript object (Number, Date, etc) based on the contents of the cell, e.g. getValue called on a date in a cell will return a Date object. — tehhowch, Mar 16 '18 at 19:12

score 0 · Answer 1 · answered Mar 16 '18 at 18:58

try this:

function calcTimeDifference(Start,End)
{
  if(Start && End)
  {
    var second=1000;
    var minute=60*second;
    var hour=minute*60;
    var day=hour*24;
    var t1=new Date(Start).valueOf();
    var t2=new Date(End).valueOf();
    var d=t2-t1;
    var days=Math.floor(d/day);
    var hours=Math.floor(d%day/hour);
    var minutes=Math.floor(d%day%hour/minute);
    var seconds=Math.floor(d%day%hour%minute/second);
    return 'dd:hh:mm:ss\n' + days + ':' + hours + ':' + minutes + ':' + seconds;  
  }
  else
  {
    return 'Invalid Inputs';
  }
}

score 0 · Answer 2 · answered Mar 30 '18 at 11:46

Thanks chirag90 for editing my question and tehhowch and cooper for providing me an answer. Unfortunately, my Javascript skills are too poor to really understand your answer.

Fortunately, I found this post on StackOverflow that is a perfect solution to my problem:

function getValueAsSeconds(range) {
  var value = range.getValue();

  // Get the date value in the spreadsheet's timezone.
  var spreadsheetTimezone = range.getSheet().getParent().getSpreadsheetTimeZone();
  var dateString = Utilities.formatDate(value, spreadsheetTimezone, 
      'EEE, d MMM yyyy HH:mm:ss');
  var date = new Date(dateString);

  // Initialize the date of the epoch.
  var epoch = new Date('Dec 30, 1899 00:00:00');

  // Calculate the number of milliseconds between the epoch and the value.
  var diff = date.getTime() - epoch.getTime();

  // Convert the milliseconds to seconds and return.
  return Math.round(diff / 1000);
}

function getValueAsMinutes(range) {
  return getValueAsSeconds(range) / 60;
}

function getValueAsHours(range) {
  return getValueAsMinutes(range) / 60;
}

Thanks again for your efforts, and perhaps you can direct me to a (online / Netherlands based) course where I can learn to use Javascript properly.

Google Form asks for duration, how to calculate with this in Google App Script

2 Answers2