Start Time and End Time Issue using ImportRange()

[brianfigg]

Hi Dave, thanks for the script. I'm an Athletic Director at a HS that is using your script to take my sporting events and push them to our school calendar... saving me time and work not updating 3 calendars, schedules and sheets. Here's my issue when using your script (FYI - I've got an IT staff member and student highly trained in coding helping me)... I have my sheet with the following columns that I'm hoping you can help fit into your script. Date, Title, Desc., Location, Start Time, End Time and other columns that don't need to go to the calendar, like transportation, refs, etc. But the problem is the script won't run when I use =importrange in your start time and end time columns. It does allow me to use import range into the Desc., Loc., and Title. Would have any suggestions how I can get it to work? Here's my test copy of your Sheet Copy of Test Cal 2

[davepar]

I'm glad to hear the script is useful for you. It's always nice to hear stories about how people are using it.

I just tried using the importrange function in the starttime column of my spreadsheet. It seems to work fine. Maybe it's a permissions problem? Does the person who is running the script have permissions for the sheet referenced in the importrange?

[brianfigg]

Thanks for your reply Dave, I'm actually running it so I'm able to Allow the Permission. I'll try it again and I'll also share w/ you the sheet with only the 5 columns that I need to import.

I just tried it w/o success. heres the file w/ the script in it and synced with my cal. https://docs.google.com/spreadsheets/d/1sN6EbcXdy8Xmmv1xPzTXs2YXhMnIGpTrYHj3xw9Ve9I/edit?usp=sharing

cal ID ulusd.org_62fvnr5qajnkt4kudafh0irb30@group.calendar.google.com cal url https://calendar.google.com/calendar/embed?src=ulusd.org_62fvnr5qajnkt4kudafh0irb30%40group.calendar.google.com&ctz=America%2FLos_Angeles

Could you see if this works for you? Or let me know what else I should try? Thanks.

On Sat, Feb 3, 2018 at 11:28 AM, Dave Parsons notifications@github.com wrote:

I'm glad to hear the script is useful for you. It's always nice to hear stories about how people are using it.

I just tried using the importrange function in the starttime column of my spreadsheet. It seems to work fine. Maybe it's a permissions problem? Does the person who is running the script have permissions for the sheet referenced in the importrange?

— You are receiving this because you modified the open/close state. Reply to this email directly, view it on GitHub https://github.com/Davepar/gcalendarsync/issues/34#issuecomment-362847278, or mute the thread https://github.com/notifications/unsubscribe-auth/AiYM2blRTtKPMvj68i9T7vGi4o8FuUL9ks5tRLNggaJpZM4R2AYr .

[brianfigg]

So I figured maybe I could just show you exactly what I'm doing and maybe you'll understand what I'm trying to do or what I'm doing wrong. So this spread sheet is for our two schools, the HS and the Middle Sch. I need to update this sheet as times and dates of games change on a daily basis. Here's my wish list if you can help with my code to this sheet. I need to send the dates(col.A) of events (col.F) in column B , ULMS to one calendar and the events for ULHS to another calendar. So, I've been creating different sheets w/ importrange to build the sheets w/ the data separated, as you can see in the different sheet tabs. I forgot to tell you that I've had the script work off and on in different sheets, w/ diff calendars, but we've (HS kid that knows code, and IT staff at school that knows a bit too), not found a consistent/reliable version. Something that I think may help my case is if the script was adaptable to use with Startdate, Enddate, Starttime and Endtime (all 4 seperate). Just wondering? I really thank you for your time and feel like I'm right on the brink of this working but then it quits.

On Sat, Feb 3, 2018 at 6:17 PM, Brian Figg brianfigg707@gmail.com wrote:

Thanks for your reply Dave, I'm actually running it so I'm able to Allow the Permission. I'll try it again and I'll also share w/ you the sheet with only the 5 columns that I need to import.

I just tried it w/o success. heres the file w/ the script in it and synced with my cal. https://docs.google.com/spreadsheets/d/1sN6EbcXdy8Xmmv1xPzTXs2YXhMnIG pTrYHj3xw9Ve9I/edit?usp=sharing

cal ID ulusd.org62fvnr5qajnkt4kudafh0irb30@group.calendar.google.com cal url https://calendar.google.com/calendar/embed?src=ulusd.org 62fvnr5qajnkt4kudafh0irb30%40group.calendar.google.com& ctz=America%2FLos_Angeles

Could you see if this works for you? Or let me know what else I should try? Thanks.

On Sat, Feb 3, 2018 at 11:28 AM, Dave Parsons notifications@github.com wrote:

I'm glad to hear the script is useful for you. It's always nice to hear stories about how people are using it.

I just tried using the importrange function in the starttime column of my spreadsheet. It seems to work fine. Maybe it's a permissions problem? Does the person who is running the script have permissions for the sheet referenced in the importrange?

— You are receiving this because you modified the open/close state. Reply to this email directly, view it on GitHub https://github.com/Davepar/gcalendarsync/issues/34#issuecomment-362847278, or mute the thread https://github.com/notifications/unsubscribe-auth/AiYM2blRTtKPMvj68i9T7vGi4o8FuUL9ks5tRLNggaJpZM4R2AYr .

[brianfigg]

here's the error we get for every row

[froznsm]

@brianfigg Hi there! I saw you starred my similar yet way messier script :) I am not very familiar with this one, but the error indicates that the value in the starttime cell of row 2 is not formatted as a date in the spreadsheet. I had the same problem in my developement.

As for your suggestion of splitting up start/end time and date into seperate columns - that really would not be necessary. When the cell is formatted to take only dates the script can parse it and directly use it in the calendar event.

Hope it helps.

[brianfigg]

Ok, I understand. Why is my formatted date column not working then. We've tried importing in every way imaginable. We're using this =CONCATENATE( text(B9,"M/d/yyyy")," ", (text(L9,"H:mm"))) to have is generate this format which is what your script is written to accept 8/13/2018 15:00 I'd really like to get this work and I'm just one step way from it working, but don't know why this won't work. here's my header row of data that I need to keep seperate, but then combine to use your script to push it to my calendar Date School Lvl Sport Opponent Title Confirmed Location Time Length End Release Start Time End Time Tue - Aug.14 ULHS JV VB Anderson Valley JV VB vs Anderson Valley @ Booneville x Booneville 5:00 PM 1 6:00 PM 8/14/2018 17:00 8/14/2018 18:00 We can use importrange into your spread sheet for all the columns except the time columns which spit our the error, unless we manually type in dates/time. If you have a copy of sheet that you said does work when you used importrange, could you share that with me to compare w/. Thank you so much.

On Tue, Feb 6, 2018 at 3:03 AM, Simon Keil notifications@github.com wrote:

@brianfigg https://github.com/brianfigg Hi there! I saw you starred my similar yet way messier script :) I am not very familiar with this one, but the error indicates that the value in the starttime cell of row 2 is not formatted as a date in the spreadsheet. I had the same problem in my developement.

As for your suggestion of splitting up start/end time and date into seperate columns - that really would not be necessary. When the cell is formatted to take only dates the script can parse it and directly use it in the calendar event.

Hope it helps.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/Davepar/gcalendarsync/issues/34#issuecomment-363388820, or mute the thread https://github.com/notifications/unsubscribe-auth/AiYM2Trv5t8nhhaYnLTMArAgA9gj6KeIks5tSDGKgaJpZM4R2AYr .

[froznsm]

@brianfigg I believe you have to manually set the right format selecting all the cells in the columns with dates in them and going to "Format->Numbers->Date and Time". The formattig of the text alone does not do this. Only when you tell Google Sheets explicitly that the data in these cells is dates does it understand. Lets see if that works :)

[brianfigg]

did you just say to "Right Justify" the column? that should work?

On Tue, Feb 6, 2018 at 8:01 AM, Simon Keil notifications@github.com wrote:

@brianfigg https://github.com/brianfigg I believe you have to manually set the right format selecting all the cells in the columns with dates in them and going to "Format->Numbers->Date and Time". The formattig of the text alone does not do this. Only when you tell Google Sheets explicitly that the data in these cells is dates does it understand. Lets see if that works :)

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/Davepar/gcalendarsync/issues/34#issuecomment-363469035, or mute the thread https://github.com/notifications/unsubscribe-auth/AiYM2ZfPQdEBgYFHFE0KRlIKbJ-Xzmwzks5tSHdKgaJpZM4R2AYr .

[brianfigg]

my IT guy is here w me now, are you available to help?

On Tue, Feb 6, 2018 at 8:02 AM, Brian Figg brianfigg707@gmail.com wrote:

did you just say to "Right Justify" the column? that should work?

On Tue, Feb 6, 2018 at 8:01 AM, Simon Keil notifications@github.com wrote:

@brianfigg https://github.com/brianfigg I believe you have to manually set the right format selecting all the cells in the columns with dates in them and going to "Format->Numbers->Date and Time". The formattig of the text alone does not do this. Only when you tell Google Sheets explicitly that the data in these cells is dates does it understand. Lets see if that works :)

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/Davepar/gcalendarsync/issues/34#issuecomment-363469035, or mute the thread https://github.com/notifications/unsubscribe-auth/AiYM2ZfPQdEBgYFHFE0KRlIKbJ-Xzmwzks5tSHdKgaJpZM4R2AYr .

[froznsm]

well, with the formatting you are sure that the script understands the value of the cell. yes im here, and available for a while

[brianfigg]

are you available for a phone call?

On Tue, Feb 6, 2018 at 8:10 AM, Simon Keil notifications@github.com wrote:

well, with the formatting you are sure that the script understands the value of the cell. yes im here, and available for a while

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/Davepar/gcalendarsync/issues/34#issuecomment-363472545, or mute the thread https://github.com/notifications/unsubscribe-auth/AiYM2Sf4vHAL35ucOnXr4MG_q8lJGfw8ks5tSHlpgaJpZM4R2AYr .

[froznsm]

I am afraid not (im in Germany btw), we could move this to some sort of instant messanger tho.

[brianfigg]

=CONCATENATE(text(A4,"M/d/yyyy")," ", (text(I4,"H:mm"))) this where we are importing FROM. is this the issue? the date and time is being importted as a text and not actaul date and time. Even though it looks like date/time

On Tue, Feb 6, 2018 at 8:11 AM, Brian Figg brianfigg707@gmail.com wrote:

are you available for a phone call?

On Tue, Feb 6, 2018 at 8:10 AM, Simon Keil notifications@github.com wrote:

well, with the formatting you are sure that the script understands the value of the cell. yes im here, and available for a while

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/Davepar/gcalendarsync/issues/34#issuecomment-363472545, or mute the thread https://github.com/notifications/unsubscribe-auth/AiYM2Sf4vHAL35ucOnXr4MG_q8lJGfw8ks5tSHlpgaJpZM4R2AYr .

[froznsm]

thats what I was trying to say yes.

[brianfigg]

ok, we're going to work on my master doc and use date/time there and see if it imports correctly, thanks for yout time. I'll be in touch

On Tue, Feb 6, 2018 at 8:15 AM, Simon Keil notifications@github.com wrote:

thats what I was trying to say yes.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/Davepar/gcalendarsync/issues/34#issuecomment-363474709, or mute the thread https://github.com/notifications/unsubscribe-auth/AiYM2RqtpoPNb4T-SiI_mUDoRXhyWz6Mks5tSHq7gaJpZM4R2AYr .

[froznsm]

Good Luck!