Format "duration" via script in Google Sheet

Question

Is it possible to format a GS cell via a script? Datas from my script are duration, but when I copy them in GS, when the value is less than 24:00 GS considers them as date, and when the value is more than 24:00 GS considers them as duration.

I can manually format the cell with duration in GS, but there is a loooot of cells, so I'd like to do it with a script.

Is it possible?

Thanks,

Adblayrog

Check the Range Class there are several ways to change format including setNumberFormat — Cooper
– Cooper, Commented Jul 13, 2021 at 14:32

Muhammet Yunus · Accepted Answer · 2021-07-13 19:25:32Z

1

You can use setNumberFormat() function.

var ss = SpreadsheetApp.getActiveSheet();
ss.getRange('A1:A10').setNumberFormat('[hh]:mm:ss');

Reference

But if your durations are in a column or a row, you can select them all and format as duration without any need to a script.

edited Jul 13, 2021 at 19:25

answered Jul 13, 2021 at 15:45

Muhammet Yunus

5894 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

adblayrog Over a year ago

Thanks for your response. I tried with .setNumberformat(), but the problem is still there : if the duration is less than 24:00, GS identifies the value like a date instead of a duration. I know I can manually format a range directly with GS, but each day I have ten new files created, and I do not want to do it manually. So I'd like a solution with a script to format a range like a duration.

adblayrog Over a year ago

Sorry, it was my mistake. I wrote without the square brackets... But now, with the brackets, I have a new problem. Ex : for 2 hours and 10 minutes, it is written "02:130:7800". So for each unit, it converts the total duration. But thanks, your response was good. I will look for the source of my new problem.

Muhammet Yunus Over a year ago

Just like you said, it converts total duration for each unit freakishly. When I tried with "[hh]:ss.mm" it worked. :)

Collectives™ on Stack Overflow

Format "duration" via script in Google Sheet

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related