Gcalendarsync

[Anastasis25]

Hi Dave, I just worked out how to open comms with you.

I posted on an old thread re: the calendarsync which is great.

The only issue is that when I do the sync, any formulas in the sheet get wiped out, which is a problem.

If there were a way for this not to happen then the script would be 100% what I need.

I can work around it, however it means undoing the sync which reverts the sheet back to it's previous state and getting the formula back, however as other people are going to use this sheet they will always have to remember doing this, which isn't ideal.

I think it works for us but would be the 100% solution if we didn't lose the forumula.

also when you do the undo you lose the latest calendar ID.

Just for your information the reason for the formula is that the data is pulled from several sources and some of the cells are concatenated to make them more useful in the calendar.

Thanks for your help/advice in advance and also for making this script easy to understand and implement.

[davepar]

So one of the calendar fields (title, description, start time, etc.) is a formula? Or is the formula in a separate column that's not used in the calendar event?

[Anastasis25]

Hi Dave,

The formula is in columns which are in the fields within the calendar.

I am happy to share the spreadsheet with you if that helps.

Regards

Anastasis Kokkinos

From: Dave Parsons [mailto:notifications@github.com] Sent: 17 February 2016 02:32 To: Davepar/gcalendarsync Cc: Anastasis25 Subject: Re: [gcalendarsync] Gcalendarsync (#3)

So one of the calendar fields (title, description, start time, etc.) is a formula? Or is the formula in a separate column that's not used in the calendar event?

— Reply to this email directly or view it on GitHub https://github.com/Davepar/gcalendarsync/issues/3#issuecomment-184729950 .Image removed by sender.

[davepar]

I see. So for example you might enter a start time, but have a formula for end time. Unfortunately, I think it's going to be pretty difficult to try to preserve formulas. The script writes all of the data back into the spreadsheet all at once. Writing to some cells and not others would be tricky.

Maybe you can just always sync in one direction, to the calendar? So always make updates in the spreadsheet and sync that to the calendar?

Dave

On Tue, Feb 16, 2016 at 3:33 PM, Anastasis25 notifications@github.com wrote:

Hi Dave,

The formula is in columns which are in the fields within the calendar.

I am happy to share the spreadsheet with you if that helps.

Regards

Anastasis Kokkinos

From: Dave Parsons [mailto:notifications@github.com] Sent: 17 February 2016 02:32 To: Davepar/gcalendarsync Cc: Anastasis25 Subject: Re: [gcalendarsync] Gcalendarsync (#3)

So one of the calendar fields (title, description, start time, etc.) is a formula? Or is the formula in a separate column that's not used in the calendar event?

— Reply to this email directly or view it on GitHub < https://github.com/Davepar/gcalendarsync/issues/3#issuecomment-184729950> .Image removed by sender.

— Reply to this email directly or view it on GitHub https://github.com/Davepar/gcalendarsync/issues/3#issuecomment-184919904 .

[Anastasis25]

Hi Dave,

Thanks for that.

I would be happy to just go in one direction so that we can make sure there is one person or set of data controlling the calendar.

I am not sure if I understand your response though, because at the moment all I am doing is syncing to the calendar and no more and this results in the forumlas being wiped out.

Are you saying that I should be able to make updates from the spreadsheet to the calendar only and this will not affect the spreadsheet formula? If this is the case then there may be something I am doing that is incorrect.

I really appreciate your time and as mentioned happy for you to have access to the spreadsheet.

Regards

Anastasis Kokkinos

From: Dave Parsons [mailto:notifications@github.com] Sent: 18 February 2016 11:07 To: Davepar/gcalendarsync Cc: Anastasis25 Subject: Re: [gcalendarsync] Gcalendarsync (#3)

I see. So for example you might enter a start time, but have a formula for end time. Unfortunately, I think it's going to be pretty difficult to try to preserve formulas. The script writes all of the data back into the spreadsheet all at once. Writing to some cells and not others would be tricky.

Maybe you can just always sync in one direction, to the calendar? So always make updates in the spreadsheet and sync that to the calendar?

Dave

On Tue, Feb 16, 2016 at 3:33 PM, Anastasis25 notifications@github.com wrote:

Hi Dave,

The formula is in columns which are in the fields within the calendar.

I am happy to share the spreadsheet with you if that helps.

Regards

Anastasis Kokkinos

From: Dave Parsons [mailto:notifications@github.com] Sent: 17 February 2016 02:32 To: Davepar/gcalendarsync Cc: Anastasis25 Subject: Re: [gcalendarsync] Gcalendarsync (#3)

So one of the calendar fields (title, description, start time, etc.) is a formula? Or is the formula in a separate column that's not used in the calendar event?

— Reply to this email directly or view it on GitHub < https://github.com/Davepar/gcalendarsync/issues/3#issuecomment-184729950> .Image removed by sender.

— Reply to this email directly or view it on GitHub https://github.com/Davepar/gcalendarsync/issues/3#issuecomment-184919904 .

— Reply to this email directly or view it on GitHub https://github.com/Davepar/gcalendarsync/issues/3#issuecomment-185468776 .Image removed by sender.

[davepar]

Now I understand. Sorry it took a while for me to catch on. After the script updated the calendar, it was overwriting all the formulas. That should be fixed now.

If you copy the script over to your spreadsheet's script again (and update the calendar ID), it should work now.

Dave

[Anastasis25]

Hi Dave,

I just ran the script again and it worked well in that the formulae were not wiped out, however what did happen is that the headings were wiped out, otherwise almost perfect.

I hope I’m not being a pain, but what you have provided is great.

Is there a way to keep the headings, then it would be perfect.

On another note, is it possible to pull two different calendars from one spreadsheet, or is that impossible or too difficult. The reason that I ask this is that ideally I’d like to have one for internal use and one for public use which would have slightly different information. It’s no big deal as can make a second sheet to produce the second calendar, then just have to sync them both. It’s just to try and keep things as simple as possible.

Thanks for your help and hopefully the headings are easy to keep.

Regards

Anastasis Kokkinos

From: Dave Parsons [mailto:notifications@github.com] Sent: 19 February 2016 02:55 To: Davepar/gcalendarsync Cc: Anastasis25 Subject: Re: [gcalendarsync] Gcalendarsync (#3)

Now I understand. Sorry it took a while for me to catch on. After the script updated the calendar, it was overwriting all the formulas. That should be fixed now.

If you copy the script https://github.com/Davepar/gcalendarsync/blob/master/gcalendarsync.js over to your spreadsheet's script again (and update the calendar ID), it should work now.

Dave

— Reply to this email directly or view it on GitHub https://github.com/Davepar/gcalendarsync/issues/3#issuecomment-185787799 .Image removed by sender.

[Anastasis25]

Dave,

Just one question in case I missed something, when you say update the calendar ID, do you mean simply sync the calendar or make a new calendar ID. Just in case this had something to do with the disappearing headings.

Regards

Anastasis Kokkinos

From: Dave Parsons [mailto:notifications@github.com] Sent: 19 February 2016 02:55 To: Davepar/gcalendarsync Cc: Anastasis25 Subject: Re: [gcalendarsync] Gcalendarsync (#3)

Now I understand. Sorry it took a while for me to catch on. After the script updated the calendar, it was overwriting all the formulas. That should be fixed now.

If you copy the script https://github.com/Davepar/gcalendarsync/blob/master/gcalendarsync.js over to your spreadsheet's script again (and update the calendar ID), it should work now.

Dave

— Reply to this email directly or view it on GitHub https://github.com/Davepar/gcalendarsync/issues/3#issuecomment-185787799 .Image removed by sender.

[davepar]

When I said update the calendar ID, I just meant if you copied the script again. It has "YOUR CALENDAR ID HERE" in the code.

Not sure why the headings were removed. I'm not seeing that problem when I sync from the spreadsheet to the calendar. Do you have more than one row of headings? And you were syncing from the spreadsheet to the calendar, or the other direction?

Syncing one spreadsheet to two calendars would be tricky. How would it determine which calendar to use for each event? Or would the events appear on both calendars, but with different title/description?

You're feedback has been great. I'm glad you reported the problem with the formulas.

[Anastasis25]

Thanks Dave,

No worries on the two calendar thing, I can see how it would be tricky. Yes the events would appear on both calendars but with different titles, the description may be the same. We will use the title to have some additional information on it for internal use only.

On the headings I only have the one row, however the formula for row 2 refers to a row 55 in the sheet that it gets the information from, i.e. it’s getting the information through a formula on another sheet. Not sure if this may affect the headings.

However when rerunning it again it does wipe out the headers and IDs, otherwise works perfectly I think. Although I do get the warning/error message that you have been trying to update too many events and the last 6 months (of 12) don’t get updated?

Regards Anastasis Kokkinos

[Anastasis25]

Hi Dave,

I tried something on my spreadsheet which worked.

As I mentioned I had formula in my spreadsheet. This was throughout the spreadsheet, except for the headings. I tested it again by making the headings formulas and it seemed to work, i.e. they didn’t disappear after I ran the update.

Does this make sense? If it didn’t have to be this way it would be better, however it is a workaround to not lose the headings in this case.

Thanks for all your help and if you resolve it great, otherwise this has been really helpful.

Is there anyway of being updated if you make improvements to this in the future or if you make some other neat apps?

Regards Anastasis Kokkinos

[Anastasis25]

Dave,

Just a quick question on uploading calendars.

When I upload my calendar I was wondering if you can set anything to make the calendar entry colour different from the default?

You can select it when you are in the calendar manually easy enough, however if you update (sync) with new info the colours are all reset.

Would be great if this was possible.

Otherwise working a treat.

Thanks for your help in advance.

Regards Anastasis Kokkinos

[davepar]

I'll try to address all of your questions:

1. For the error about trying to update too many events, I increased the "sleep" amounts in the script. This will make the script run a bit slower, and hopefully help with that problem.
2. Disappearing headers/formulas. I've made another fix that will hopefully fix this issue. Now the script only updates the event IDs. Nothing else in the spreadsheet should be touched now. You shouldn't need to make the headers into formulas (but I see why that would have worked before).
3. I'm not sure how to get updates about this script. I don't plan on adding any new features. I'll just be doing bug fixes as people report problems.
4. I put the event color problem into a new issue, since this thread isn't getting so long. I'm not sure when I'll have time to look at that. https://github.com/Davepar/gcalendarsync/issues/5

[Anastasis25]

Thanks Dave,

This is great,

One question, where do I get the latest script from?

Colours would be nice to have but otherwise it’s excellent.

Regards

Anastasis Kokkinos

From: Dave Parsons [mailto:notifications@github.com] Sent: 27 February 2016 04:31 To: Davepar/gcalendarsync Cc: Anastasis25 Subject: Re: [gcalendarsync] Gcalendarsync (#3)

I'll try to address all of your questions:

1. For the error about trying to update too many events, I increased the "sleep" amounts in the script. This will make the script run a bit slower, and hopefully help with that problem.

2. Disappearing headers/formulas. I've made another fix that will hopefully fix this issue. Now the script only updates the event IDs. Nothing else in the spreadsheet should be touched now. You shouldn't need to make the headers into formulas (but I see why that would have worked before).

3. I'm not sure how to get updates about this script. I don't plan on adding any new features. I'll just be doing bug fixes as people report problems.

4. I put the event color problem into a new issue, since this thread isn't getting so long. I'm not sure when I'll have time to look at that.

   5 https://github.com/Davepar/gcalendarsync/issues/5

— Reply to this email directly or view it on GitHub https://github.com/Davepar/gcalendarsync/issues/3#issuecomment-189383273 .Image removed by sender.

[davepar]

You can get the latest script at: https://github.com/Davepar/gcalendarsync/blob/master/gcalendarsync.js

[Anastasis25]

Thanks Dave,

I am a bit slow on all this stuff.

Regards

Anastasis Kokkinos

From: Dave Parsons [mailto:notifications@github.com] Sent: 27 February 2016 09:22 To: Davepar/gcalendarsync Cc: Anastasis25 Subject: Re: [gcalendarsync] Gcalendarsync (#3)

You can get the latest script at: https://github.com/Davepar/gcalendarsync/blob/master/gcalendarsync.js

— Reply to this email directly or view it on GitHub https://github.com/Davepar/gcalendarsync/issues/3#issuecomment-189506750 .Image removed by sender.

[torlogg]

Hello Dave, First of all, big thanks for the script - it works great. I have a question similar to Anastasis' - would it be possible to sync multiple calendars with/from one or more sheets? I manage quit big radiology department (+40 regular staff, up to 100 radiologists), and our monthly schedule contains couple hundred "work shifts" events, which now, thanks to you, can be easily sync'ed with department calendar. All events/shifts belong to one of 4 categories - let's say "morning", "afternoon", "night", "holidays". All I need now is that the script determines to which category an event belongs to and put all events of one category into corresponding calendar, one of 4 calendar possible. I keep all "category" data in "Description" column. I came across same question on other site: http://stackoverflow.com/questions/28840397/export-events-to-multiple-google-calendars-from-google-spreadsheet but since I'm no programmer it's all magic to me. Thank you! Regards Adam