Tweak to Sync to Different Calendar from Each Sheet in a Workbook - Not an Issue

[laraklee]

1. THANK YOU, Dave! This is great, and it is going to be fabulous as we try to push the boundaries of Google Classroom as a quasi LMS.

2. Novice that I am, I tweaked the script a little so I could use a master sheet in a workbook with separate sheets for different sections (which are on different sequences because of our rotating period type schedule).

I reordered the "get spreadsheet" and "get calendar" variable assignments so that the current sheet stuff came first. Then, I added this line:

// Get calendar and events var calendarId = sheet.getRange(sheet.getName()+"!B2").getValue();

to assign cell B2 from the sheet (being careful not to put a date in the column) to the calendarId instead of having it a global variable. See sample below. It also required setting "skip blank rows" to true.

Notes: It works great going in both directions for this function, but I discovered that since I was using an ImportRange to bring in my events for each specific calendar from the master, I could only really use it to sync from the Sheet to the Calendar - otherwise syncing back replaced my ImportRange with just text.

Again, I thank you for both the working function and a fun challenge of learning something new. As a newbie, I'm sure there were better ways to do this, but it works, and it works for me. Any suggestions for improvement would be welcome.

Mahalo nui loa! (Thank you very much in Hawaiian - which is where my school is located.)

[laraklee]

_Another Note: I thought it would be great to have it autoUpdate using a time trigger, but I couldn't figure out how to get it to trigger onEdit when my importRange data changed - rather than onOpen or when a user actually changed the workbook. I expect there was another (probably more elegant) way to make this work, but since we don't have to conserve every line of code anymore, this worked for me.

• Set up global variables for the sheets in my workbook I wanted to autoUpdate. I know it is possible to use a SpreadsheetApp function to pick out sheet names, but I only want some of the worksheets to be run with this. There's a bunch of other info in these sheets including the user instructions for my colleagues that isn't relevant.

var plannerId = '1Sd87vhnjTUYm33CRyTXr4ujWm4fTt9CLolvAdRbJwso';_

// These are the names of the sheets in the plannerId workbook that you want to sync. Add in any other sheets you want to use, and update the sheets variable to match. This workbook contains the master calendar and individual sheets for all the different schedule variations - along with some other stuff. // Blanks in the sheets variable will cause an error on execution. I'm sure there was a way to trap that error, but I don't understand enough, yet.

var sheet1 = 'S2 P1/2 Gcal Sync'; var sheet2 = 'S2 P8 Gcal Sync'; var sheet3 = ''; var sheet4 = ''; var sheet5 = ''; var sheets = [sheet1, sheet2];

- Hit the net for a function that would cycle through specified sheets. Saved me having to figure out the For/Next syntax on my own.

function autoUpdate(){ for (var s in sheets){ var sh = SpreadsheetApp.openById('1Sd87vhnjTUYm33CRyTXr4ujWm4fTt9CLolvAdRbJwso').getSheetByName(s); syncToCalendarAuto(sheets[s]); } }

- I made a new copy of the syncToCalendar function, added in the argument from autoUpdate for the sheet name, picked the plannerId out of the global variables, and renamed the function syncToCalendarAuto

function syncToCalendarAuto(sheetName) { console.info('Starting sync to calendar'); var scriptStart = Date.now();

// Get spreadsheet and data

var spreadsheet = SpreadsheetApp.openById(plannerId); var sheet = spreadsheet.getSheetByName(sheetName); var range = sheet.getDataRange(); var data = range.getValues(); var calendarId = sheet.getRange(sheet.getName()+"!B2").getValue(); if (data.length < 2) { errorAlert('Spreadsheet must have a title row and at least one data row'); return; }

// Get calendar and events var calendar = CalendarApp.getCalendarById(calendarId); if (!calendar) { errorAlert('Cannot find calendar. Check instructions for set up.'); } var calEvents = calendar.getEvents(beginDate, endDate); var calEventIds = calEvents.map(function(val) {return val.getId();});

_... All the rest of the code remains yours intact. I understand the gist of what you did, but not enough to really play with it. Your developer/user notes in the code are fantastic for a newbie.

Any suggestions for the newbie to improve this would be welcomed!

Thank you again! I'm supposed to be grading papers, but this was far more challenging and fun.

My next project will be to automate the set up of my planning master so I don't have to generate the other variations of the schedule by hand anymore - not sure where to start on that, though, so that will have to wait for summer, probably._

[joelando]

Hi there,

New to Google Script. Could you share your full script and GoogleSheets document as your instructions aren't that easily to follow for a newbie!

Thanks

[davepar]

@laraklee Sorry it took so long to take a look at this. These are great changes to the script for handling multiple sheets. I'm glad you enjoyed the challenge. A lot has changed since February, so maybe you're not using this any longer. I'll take a look at it anyway.

Just to clarify, it looks like you have multiple sheets with some containing events and others containing non-calendar stuff. You'd like to sync each event sheet with a separate calendar.

I like your idea of putting the calendar ID in the sheet data itself, but I think I'd put it in cell A1. This way you could hide line 1 of the sheet and have the code skip over it.

And then to determine which sheets have event data versus other stuff, I'd have the script look in cell A1 of each sheet and see if it contains a string that looks like a calendar ID. That would be more flexible than coding the sheet names in variables.

@joelando If that sounds like it would work for you too, I'll look into making the changes today. I don't have much going on this week.

One caution though. Each run of the script has a time limit, which puts an upper bound on the total number of events that can be processed before hitting an error. I'm not sure what the limit is, but as long as there are less than a thousand total events it should be ok. This is regardless of having one sheet or more.

[laraklee]

Thank you for the tips on script run time. So far I haven't had any issues.

I'm definitely still using this with modifications (though with distance learning and so forth, I haven't looked at the code since February and have probably forgotten things.), and since someone else is interested, here's what I'm using right now. I tried to put together a Google Sheet (attached, I think) so that some of my colleagues at school could check it out this summer. Some of what I have in there is specific to how our schedule works.

The Google Sheet should be viewable to anyone with the link...

https://docs.google.com/spreadsheets/d/1UuRH23qqYDW0uPLbxZHzratK4kRs1LhskhnW9C3jexg/edit?usp=sharing

(The MCS is a download of our school's master calendar, and all the repetitions are to handle our 8 period schedule.)

Here's my current copy of the code:

https://gist.github.com/laraklee/ba0498d17207dff2850ccc9bfd57a053

[davepar]

An easier way to share code is to paste it into a "gist" at https://gist.github.com/ and then link to it here. Or surround the entire block of code with triple back quote so that indents are preserved.

[laraklee]

Thank you. Let me log in shortly and see if I can edit my comment.

[davepar]

Much better. Thank you!

[laraklee]

Thank YOU - for doing the full code. I just tweaked it. :)

On Thu, May 14, 2020 at 11:06 AM Dave Parsons notifications@github.com wrote:

Much better. Thank you!

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/Davepar/gcalendarsync/issues/62#issuecomment-628886628, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABEWGCTSAGJYSNCUCU6N5MLRRRMM5ANCNFSM4KWASOGQ .

-- Lara K. Lee Science Department [image: Related image]

[joelando]

Thanks guys. Still getting to grips with Script and this is helping.

@Davepar. Which is the most up to date version of the code? Are there instructions of how to setup with A1 as calendarID in it?

Joel

[laraklee]

My work on this was my first time scripting, too, and I'm no programmer. 😀 What I posted is what I'm using right now with my students. I'm sure once Dave gets done, his version will be cleaner, more general, and more elegant, and I'm looking forward to seeing how I should have done this. I don't actually understand his whole code--just enough to modify it to do what I wanted it to. His descriptive comments in the code were essential!

If you want to try my code now, the instructions I was working on for my colleagues are on the Use Notes of the spreadsheet. It's supposed to step them though the set up, but I haven't had anyone try it yet who didn't already know scripting.

Just to clarify, the spreadsheet is designed to take a basic sequence of school day plans and generate appropriate sheets to sync to Google Classroom calendars for our 8 period rotating schedule. That's what is on the master sheet. There was probably a way to do that using scripting, but I didn't know how, and still don't.

The part that has to do with gcalendarsync is running the updates to different calendars from each sheet.

I also haven't looked at the instructions or code since February, but definitely ask if something doesn't make sense or doesn't work when you try it, and I'll go back into the code and try to figure it out.

[laraklee]

P.S. As a follow up to the run time limit issue, would I be correct in assuming that I'll get the usual error notification if that happens?

Thanks!

[davepar]

The new version of this script on the GSuite Marketplace now fully supports multiple calendars as originally suggested by Lara, so I'm going to close this issue.