Excel to CSV - Google Calendar

Afternoon all.

Looking for a little guidance if possible. I currently setup and maintain our team's roster on a yearly basis and for next year it has been requested that I look into adding the ability to import to people's Google calendar.

Now I've had a look at this and the basic principle seems simple enough, export to CSV and import to your calender. The formatting also looks simple enough.

However based upon how I have my spreadsheet setup ATM ( see attachment ) I'm not quite sure how to go about this.

Each person would need to be able to export their data (or I do it) and for them to import it into their calenders.

The guide Google provides is great:
support.google.com/calendar/answer/37118?hl=en

However a little guidance to get me started would be fantastic thank you.

Looks like two steps are probably needed: first swizzle the data to the same table layout that google is looking for, and second export the data to a CSV file that google can read. Unless you/your users want to a lot of hand waving each time or your calendar is in a fairly strict layout, you probably want to do both steps with VBA.

Thank you for the reply Gregor.

I've started the process of "swizling data" as you call it via some formulas, as I'm not quite up on VBA, least not until I dig my book out.

For some reason I cannot get the autofill to work properly. On my CSV test page I am calling for data that is horizontally displayed on the roster, but for the CSV file I need the data presented vertically. See attached picture, when I drag down to autofill it originally moved up in cell letters and number; however I wanted it locked to row 5 so added a "$" to the formula infront of the number to freeze it. Great I thought, however now when I drag the autofill down in simply copies the formula as is, not increasing the cell range at all; as if I copy and pasted the formula. I'm confused..

yeah, that's the tough part because you kinda want quash and transpose the data to get it into the tabular form that google is looking for.

it would be easier for me to picture what's going on here if you could post an xlsx file with two tabs, the first tab an example of the calendar data as you have it and the second tab with the columns google is looking for and a couple of the rows filled in using the first tab.

Thanks for the reply Gregor.

Went through the whole transpose option last night, which really is not a great solution.

Thankfully I have almost completed the transfer over using formulas, with the help of others I found a solution to the hoz to vert issue.

https://www.excelforum.com/excel-for...ml#post4937311

no problem; also I'd try saving a small hand keyed sample to CSV to make sure that Excel's vanilla "save as CSV" feature is a the flavor of CSV file google is happy with.

I've performed several small test .CSV files and imported them to Google calendar. Its best to create a new calender before you import the roster so as to have full control over the format and ability to hide/delete the roster. After a tweaks it's as good as I can get it..

The main issue was that because I need to have 365 days on the form, due to shift changes any blank records IE ones that just had a single date and no data; (the date field is the only static, non formula field) the calender would show these as "my event or no title" throughout the year.

The solution was to save as "CSV comma delimited" close the original and then open up the saved CSV, then manually remove all the blank records. A little bit of a pain, but easy enough.

The only thing that bugs me is day shifts don't show as a full block of colour like nights, but there is sod all I can do about that.