| Getting time from a cell in Google Spreadsheet |
idev |
6/8/10 5:55 AM |
Hi I have created a Google Spreadsheet form in which for one of the fields I get the time (hh:mm:ss) in 24-hour format. I am using Google Apps Scripts to read values from this spreadsheet and put it into another file. But the other file gets the en |
| Re: Getting time from a cell in Google Spreadsheet |
Evin Levey |
6/8/10 7:56 PM |
idev, the spreadsheet returns a Date object, not a string. Right now we do most operations in Pacific Time Zone, so you'll need to adjust the time accordingly. You can try Utilities.formatDate() function. Evin |
| Re: Getting time from a cell in Google Spreadsheet |
idev |
6/10/10 6:37 AM |
Thank you Evin. Really appreciate the quick help. I am facing issues with getting the data in a different time zone. The time entered in a cell is 17:30:00. I am trying to convert it into a GMT+5 timezone. The date from the cell is read as Dec 30 18 |
| Re: Getting time from a cell in Google Spreadsheet |
idev |
6/10/10 6:40 AM |
One more question. If I were to calculate the time in GMT-5:30 hours, how would I write the format statement? Tried Utilities.formatDate(rowValues[0][10], "GMT-5:30", "HH:mm:ss"); Utilities.formatDate(rowValues[0][10], "GMT-0530", "HH:mm:ss"); Utili |
| Re: Getting time from a cell in Google Spreadsheet |
ppettigrew14 |
5/30/11 7:57 AM |
Hi Idev. I'm sure that there is a more elegant way of doing this, but I did figure out that you can play around with the time zone by adding a fixed number after the "GMT" element in the function. For example, I am on the east cost. The correct ti |
| Re: Getting time from a cell in Google Spreadsheet |
DaNamiel |
8/31/11 12:54 AM |
Hi idev, It's already a year. How's your finding on your issue? I am particularly interested in your question 1 and 2 in your first reply, namly, |
| Re: Getting time from a cell in Google Spreadsheet |
Henrique Abreu |
9/1/11 5:44 AM |
Let's start by understanding better how dates/times works. Spreadsheet date-time: First, there's no "time" only value on a cell. The date is always there, you just don't see it. Let's do some exercises to get the idea better. Type zero on a cell and |
| Re: Getting time from a cell in Google Spreadsheet |
DaNamiel |
9/1/11 8:43 AM |
Thanks so much for your wonderful reply, Henrique! Actually, I have read your reply in another thread* which also explained the concept of date and time in GAS in detail. However, the problem that I am facing is not the handling of timezone between |
| Re: Getting time from a cell in Google Spreadsheet |
Henrique Abreu |
9/1/11 10:37 AM |
@DaNamiel I agree that it is a clear bug! An easy workaround is add the correction yourself (-1404), but if you can't guarantee that all your users are in Hong Kong. I guess you could... actually it's a great workaround! :) I've noticed that the cra |
| Re: Getting time from a cell in Google Spreadsheet |
serge insas |
9/1/11 1:49 PM |
Hi Henrique,Brilliant explanation as usual ;-) @DaNamielconcerning that problem that I've struggled with quite a few time I did use another workaround that could be use in your exchange with Gcal.I made a sheet to modify events in a Google Calendar t |
| Re: Getting time from a cell in Google Spreadsheet |
DaNamiel |
9/2/11 12:44 AM |
Thanks Henrique and Serge for your workarounds! Before I read your posts, I wrote another workaround for this issue, which makes use of the trick that the weird offset (1404 sec) is gone when timezone is set to "Asia/Tokyo". So whenever before the g |
| Re: Getting time from a cell in Google Spreadsheet |
serge insas |
9/2/11 3:18 PM |
hi again,About your last "bug report", I did notice that there were some strange things happening with date objects displayed in the logger... that's one of the reasons I don't use the logger for dates anymore because there are continuous conversions |
| Re: Getting time from a cell in Google Spreadsheet |
DaNamiel |
9/2/11 9:34 PM |
Hi Serge, You're right, it's really just the problem on the Logger, so when I work with those dates and times without touching Logger, it's all right! But it's really an issue that Google should solve as Logger is usually used for debugging... For th |
| Re: Getting time from a cell in Google Spreadsheet |
serge insas |
9/3/11 4:11 AM |
Hi Daniel, that's good news finally !Just one detail : do you use separate columns for date and time to display the events details or to edit those values (or to define them) ?or both ?In one of my very first spreadsheet using Gas I did something sim |
| Re: Getting time from a cell in Google Spreadsheet |
DaNamiel |
9/3/11 7:45 AM |
Hi Serge, In my spreadsheet, I put date and time in separate columns. The date column contains only the date portion while the time column contains only the time portion, so that's not the approach in your shared spreadsheet. Actually, I could simply |