1

One cell in a Google Sheets document holds a value given by a SUM of cells formatted as "elapsed hours":"elapsed minutes". Attempting to make calculations in a custom script on this cell will throw errors. Further investigation using typeof() function will show this as "object". How can I convert the cell value to number? Attempted parseInt() or parseFloat() unsuccessfully.... In the linked sheet here you will see that:

  • cell A1 having a formula that returns 288:00 formatted as duration,
  • cell A2, having =daysHrsMins(A1), that returns a negative value, -3.179.700.519.840.000,00 from the following function:

    function daysHrsMins(input) {
       return input*1440;
    }
    

Regional settings: Italy
Timezone settings: (GMT +1) Paris

  • 1
    Add a minimum, complete and verifiable example (add the minimum code necessary to reproduce the problem) and include the textual error messages that you get. – Rubén Oct 19 '17 at 19:37
  • See here: docs.google.com/spreadsheets/d/… - A2 will return type object and calcs are scrambled – Riccardo Oct 19 '17 at 20:04
  • While a link to spreadsheet is helpful, questions should be self contained. Please edit the question to add the relevant information. – Rubén Oct 19 '17 at 20:20
  • Looks that the custom function is working fine. What are the errors that you get? – Rubén Oct 19 '17 at 20:29
  • Why do I get a negative value? – Riccardo Oct 19 '17 at 20:31
2

Short answer

How can I convert a cell value to a number?

On Google Sheets durations are just a display format for date values, so when a custom function takes a duration it's treated as a date object.

Explanation

Doing arithmetic operations

When we are doing arithmetic operations on dates/times/duration, we should bear in mind the following:

  • Google Sheets

    • Use day as the unit
    • Use December 30, 1899 0:00:00 as the zero.
    • Doesn't handle timezones, but spreadsheets has a timezone setting.
  • Google Apps Script / JavaScript

    • Use milliseconds as the unit
    • January 1, 1970 0:00:00 in UTC as the zero.
    • Date object includes timezone.
    • The Script project has a timezone setting.

Display format

  • Google Sheets

    • Has a duration format ([h]:mm:ss.00).
  • Google Apps Script / JavaScript

    • Has the Utilities Service and formatdate method.
    • Hasn't a built-in duration format.

Transfer of dates between Google Sheets and Google Apps Script

When a date value is transferred from Google Sheets to Google Apps Script or vice versa it is automatically converted considering the timezone of the spreadsheet.

Solution

As Google Sheets and Google Apps Scripts duration/date handling is different the simplest solution is to do the conversion from date/duration to number on the origin application.

On Google Sheets we could use TO_PURE_NUMBER to do this conversion:

=daysHrsMins(TO_PURE_NUMBER(A1))

For certain use cases it could be convenient to use a JavaScript library like moment.js

References

Related Q&A from this site:

Related Q&A from Stack Overflow

  • Thanks Ruben, I thought that the format is just applied when displaying values, without altering the cell/variable type. Is there a way to pull an "object" and convert it accordingly? – Riccardo Oct 20 '17 at 8:14
  • I have attempted to access the cell using getHours() function, but the result is wrong. Changed the function as follows function myHours(input) { return input.getHours(); } – Riccardo Oct 20 '17 at 14:03
  • @Riccardo: I updated my answer. – Rubén Oct 20 '17 at 15:20
  • I have changed the function to function daysHrsMins(input) { return TO_PURE_NUMBER(input); } and now I get an error: "Error ReferenceError: "TO_PURE_NUMBER" is not defined. (line 2)." – Riccardo Oct 22 '17 at 19:16
  • @Riccardo spreadsheet built-in functions doesn't work in Google-apps-script – Rubén Oct 22 '17 at 19:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.