Google Sheets – How to Cast Cell Value from Object to Number

google sheetsgoogle-apps-scriptgoogle-sheets-custom-functionjavascript

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

Best Answer

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

Related Topic