Google Sheets - Add (Import) Events in Bulk to Google Calendar Using Apps Script Tutorial - Part 11

[@markuswinter-cdps3008]

Another question about syncing/exporting google sheets to google calendar. Can you make a video about how to include the colors from google sheets into the calendar? As an example if you have a series of events in google sheets that you want to import into calendar and have them highlighted the same color in calendar as they are in sheets. Thanks as always for your videos!!

[@rmu1985]

Hello, you videos are really helping us in learning google sheets. Keep them coming and you are doing a great job. Can you do a video on integrating Google tasks Api? Breaking talking for another Google ID and getting the status of tasks and updating the task status in sheet?

[@consultingsupport1096]

Love your presentation of app script. I enjoy much how faster things work. Is there a way to add events to multiple calendars?

[@allinoneallinone3233]

Hi guys, Love your videos. Can you please update this one and see it is possible to invite some "guest" to a particular event, so all of the participants can receive a notification alerts regarding the event (let's say 5 min before it)?

[@stevenboss3813]

Fantastic and thank you, great presentation. Please can I ask, on your last line "description: data[i][4]});" is there a way of including a description from a 6th column in sheets?

[@diegodelfino9409]

how you prevent the script to duplicate the events already added to the calendar. let's say I add another event to the excel file and rerun the script, it will duplicate all the previous entries. thanks

[@wakeyourdata2812]

Your videos are amazing!!! We use a Google Form for our managers to submit events to be added to our calendars. There are 60 calendars. Is there a way to add in some logic to what you demonstrated so that if certain fields contain certain text it will populate the event to the appropriate calendar? (i.e., If the Marketing column is Yes and the store field contains the word Chicago then the event will populate to the Chicago Marketing calendar?) If not, is there a way to write the macro so that it distributes the events to the appropriate sheet in the worksheet then we can use Zapier to populate the calendars. I'd prefer to avoid using Zapier.

[@ExcelGoogleSheets]

The short answer is yes.

[@markuswinter-cdps3008]

Thanks, as always for your videos!! Can I request that you do a video, not so much for events, but for scheduling. We use google sheets for scheduling, and rather than duplicating efforts (and manually entering the schedule into Google Calendar), it would be great to be able to export it. And if you are able to, can you include days off. Reason that I ask is because I've run into the error where will not execute if it's a string (text), rather than date/time (value). To try to get around this, I used if([cell reference]="off", 0...but I'm sure there's a better way than using 12/30/1899 :D. Thanks again!!

[@tonyparini]

Nice video! However: is it possible to do the same thing using different calendars?

[@abdullahquhtani4247]

Hi. I hope you REPLY :-) That’s great put you missed something. What will happen when new events added if for example is connected to google forms! What will happen in this case? Will the script delete the previous ones or overwrite them? shouldn’t be there if condition to add only new added event based on a field to be changed after the script add the event to the calendar? It’s a good point to be considered. Thank you.

[@johanvandervorst]

Can you please add a video where you explain how to update the events in the calendar from the sheet?!

[@elbiju]

hello, did you now how to update the events ?

[@rogeriolimacarneiro3068]

@@elbiju you can run this code and, so, create a trigger to run each min.

[@derekherzog1569]

you could just pair this with the deleteEvent() method to effectively do the same thing

[@krisannespedillon7533]

How can I add the addGuest?

[@Nika-cp9ll]

if I change the date or time in google sheets, will it be automatically updated in google calendar?

[@golammusabbir914]

Hi.. I love your training. it makes me smarter. i m very much thankful to your effort that you are giving to us. In this script can u help us to avoid duplicate event and/or deleting any event

[@gum3213]

did you fixed this problem?

[@khatushyamunitedpacking2468]

dear sir, when we are run this code , then error occur this , (Exception: The parameters (String,String,String,(class)) don't matchthe method signature for CalendarApp.Calendar.createEvent. (line 48, file "Code")) please needs to help for same.

[@pablomoralestorricos9433]

same problem, did you solve it?

[@kimberlyonza8121]

Thank you so much!!

[@BluechipJunior]

Would you consider posting the code itself so we may copy and paste? I know I am being very lazy! PS- Thank you so very much for your amazing tutorials!!! I have utilized them.

[@gringotaxis2296]

These are a great series of videos! However I'm getting a SyntaxError: Unexpected end of input on the last line. Any help on figuring this out would be great

[@henryvongsavath1706]

Post a link to your code, maybe I can help you out.

[@ИльяСултанов]

@@henryvongsavath1706 function event() { let ss = SpreadsheetApp.getActiveSpreadsheet(), DbCalendar = ss.getSheetByName('DbCalendar'), shedule = ss.getSheetByName('shedule'), shows = ss.getSheetByName('shows'), stuff = ss.getSheetByName('stuff'), calendarId = stuff.getRange('H2').getValue(), eventCal = CalendarApp.getCalendarById(calendarId); let lr = DbCalendar.getLastRow(), lc = DbCalendar.getLastColumn(); let data = DbCalendar.getRange(1, 1, lr, lc).getValues(); for(i = 0; i

[@Stirlinggorilla]

How can I add events that repeat weekly/monthly depending on information in the google sheet

[@jenniferradcliffe9085]

how do you add events that are a combination of all day events and specific time events?

[@ishanharshvardhan6687]

is it possible to automatically sync a sheet to calender as rows get added

[@yo_swarnali]

This code is for one calendar(or one gmail account) only. What if I want/need for more than one gmail ids? Means, I want to send calender events to many people.

[@nhatkhiemlai3650]

Why my function show Cannot read property 'getTitle' of undefined?

[@wongkinwah1052]

I started learn your videos tutorial from Google Sheets till Script now, really helpful, I have improved a lot. Thank you very much. I encountered a problem can't be solve, hopefully Sir could give me an advice. I have created 218 events in Google Sheets and desire to create all those events in Google Calendar, I can't create events in a bulk, how do I solve this problem?

[@ExcelGoogleSheets]

Did you try the example in the video?

[@wongkinwah1052]

@@ExcelGoogleSheets I followed exactly as video, no problem at all until exceed 25 events. Anyways, I could solve my problem by using "utility.sleep" command. Thank you Sir.

[@ugesazoom6169]

Hola: Copié el còdigo. (Creo que bien). Lo pueden verificar viendo el video. Se los dejo aquí para que lo re utilicen luego de corregir los errores que pueda tener por mi que no me haya dado cuenta o por algún motivo. Yo lo probé y en la depuracion de la funcion getEvents me sale "las coordenadas o dimensiones del intervalo no son validas" : ss.getRange(2, 1,lr-1,5).clearContent(); En el video se ve el inicio del codigo en el minuto 12:19. Luego en la depuracion de la funcion addEvents me sale el error que dice "No se puede encontrar el metodo createEvents(string,string,string,object)" en esta linea de codigo : cal.createEvent(data[i][0], data[i][1], data[i][2], {location: data[i][3], description: data[i][4]}); que en el video se puede ver terminada en el minuto 16:51. Por lo demás no surgen errores en la depuracion. Aqui les pego el código entero con las diferencias de la fecha en la primer funcion (no se si puede afectar en algo y no se si cada vez que se quiera ejecutar hay que actualizarla o para que està pero bueno) siguiendo por el cambio de posicion de los datos de dia y mes que los anglosajones ponen primero el mes y luego el dia (supongo que esto no afecta en nada si se le da el mismo formato a la celda donde estarán las fechas y horas) y la otra diferencia es la id del calendario sobre el que quiero trabajar (cada cual pondrá la id que corresponda a su calendario así que es irrelevante). Bueno espero que les sirva y si les es posible ayudarme a entender que está mal en el código para depurarlo correctamente y funcione. Muchas gracias a todos. Codigo: function getEvents(){ var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var cal = CalendarApp.getCalendarById("[e-mail]"); var events = cal.getEvents(new Date("27/12/2017 02:16 AM"), new Date("27/12/2017 02:30 PM")); var lr = ss.getLastRow(); ss.getRange(2, 1,lr-1,5).clearContent(); /**ERROR DE DEPURACION**/ for(var i = 0;i

[@santosahernandezmendoza7147]

Me funcionò!

[@CesarPeron]

Funciona!, se ve que en la variable getRange("A2:10" + lr) iba la columna E y no el 10, me sonó raro y lo mas extraño que en el video le funciona, gracias por compartir

[@IsaacTannerDempsey]

Can we download this from your website?

[@enzymeone1240]

Any video about delete events for ex by ID ?

[@huynguyen7048]

Curious face.I have the same question

[@huynguyen7048]

got it. You can run this code to remove old events before running addEvents function: var events = cal.getEvents(new Date ("02/8/2019 12:00 AM"), new Date("02/28/2019 11:59 PM")); for (var i=0;i

[@asyrafsyahmi9162]

Can I know how to create event in Google Calendar when it meet specified criteria in google sheet? Request leave for example. It create new events when only the request is approved.

[@derekherzog1569]

you could do that with a trigger I'd think, see how to do that here: stackoverflow.com/questions/26187361/triggering-script-by-specific-cell-value

[@mohammedzaid3878]

How to get the events from all domains?

[@catherines4932]

This Google Apps Scripts for Sheets playlist is brilliant! for the addEvents function, I've used .getSheetByName("Sheet2") for the function to work. Despite clicking and saving Sheet 2, .getActiveSheet() wasn't working for me. Are there any common reasons for this? What makes the class know what my active sheet is?

[@ExcelGoogleSheets]

Active sheet should be whatever the last selected sheet was in the spreadsheet.

[@itstigerplaysyt69420]

Everytime I add some events then run the add events code, the previous events keeps on duplicating after every run. How do I fix it?

[@huynguyen7048]

Curious face.I have the same question

[@huynguyen7048]

Got it. You can run this code to remove the old events before running addEvents var events = cal.getEvents(new Date ("02/8/2019 12:00 AM"), new Date("02/28/2019 11:59 PM")); for (var i=0;i

[@bperotta]

Try this (create a status column): ... for(var i = 0;i

[@AnkitKumar-pk8tk]

@@bperotta is there any way that everytime I add a new row in spreadsheet it automatically create event in my calendar and I don't have to run it again and again Please help me

[@bperotta]

@@AnkitKumar-pk8tk maybe you can add a Trigger, by clicking on "clock" icon in the javascript. Choose, for example, every 1h, 1 minute, or at one specific time eg. 3 AM. The problem with trigger by specific time is that the script could add an incomplete event. A conditional could resolve this (if all cells in a row has a content, then run script).

[@christianebers]

Is there an easy way to split the date and time into two columns? If so can someone reply with the logic! ty

[@sevaricofrathe5733]

I had to do this. It's easier to redesign the original sheet to format it as time. I was given 0755-0830 for all the times. I used =SPLIT("-",A13,true,true) to get 0755 and 0830 in different cells. Then had to use 2 tables and if statements to convert the 0755 (number) into 07:55 (time). THEN assembled all the parts back together. Good luck.

[@mariyamego9613]

forEach does work. You just have to attach it to an array, i.e. data.forEach()

[@ExcelGoogleSheets]

Did I say it doesn't work?

[@mariyamego9613]

@@ExcelGoogleSheets yeah, around 9:37

[@ExcelGoogleSheets]

oops :) I stand corrected.

[@fantouch]

One of my accounts is with a not gmail user, when I tried to open scripts from googlesheets it shows me an error. It is because I’m not using a @gmail.com??? The error page redirect me to google drive

[@ExcelGoogleSheets]

gmail has nothing to do with it. You need to make sure that account has proper permissions to access the script.

[@dextersocc]

Is it possible to modify the code to configure the color of each event?

[@derekherzog1569]

It is; you can view color properties here: developers.google.com/apps-script/reference/calendar/event-color

[@pichit.raetai]

Thank you

[@varunsethi5662]

Sir why dont you share the codes too .. thanks

[@fantouch]

I am having problems, LastRow is giving me a lot of blank rows . I do not know why. I bring the information with a query from another sheet. (When A is not Null). but the script when I run it gives me the information of all the blank cells. [,,,,,,,,] like this. why lr = ss.getLastRow(); is not working? function addEvents() { var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var lr = ss.getLastRow(); var data = ss.getRange("A2:G" + lr).getValues(); for( var i = 0; i 1000 (numer of rows in the file)

[@ExcelGoogleSheets]

Try this function doStuff(){ var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var lr = getLastNonEmptyRow(ss,1); Logger.log(lr); } function getLastNonEmptyRow(sheet,column){ var lr = sheet.getLastRow(); var rngset = sheet.getRange(1, column, lr, column).getValues().map(function(d){ return d[0]; }); var pos; for(var i = rngset.length-1;i>=0;i--){if(rngset[i] !== ""){pos = i+1; break;}} return pos; }

[@fantouch]

I use all of that above .... function addEvents() { ..... and remove " var lr = ss.getLastRow(); " from the function? or I use all of that inside " function addEvents() {" ??

[@fantouch]

BTW thank you for go the extra mile

[@fantouch]

It works, Good

[@ExcelGoogleSheets]

OK, cool, I'm glad you figured it out.

[@phoenixempire8886]

🙏🏻

[@rishirishi4970]

Thanks

[@kollosal786]

not sure if this was already tackled on later parts but Google servers sometimes gives an error for fast event creation. Error: “You have been creating or deleting too many calendars or calendar events in a short time.” Adding Utilities.sleep() for every event created helps. Here's a reference: stackoverflow.com/questions/29308656/error-you-have-been-creating-or-deleting-too-many-calendars-or-calendar-events

[@ExcelGoogleSheets]

Great tip!

[@marcobanderas3306]

I'm getting the following error all the time: "Exception: Invalid argument: startTime", although I followed your tutorial twice ;/

[@ПавелПитерский-д7г]

Thought (looking at the video's title) that you find out the way to make a bulk creation\updating calendar events... heh) sad it's not... but cool video never the less

[@derekherzog1569]

Hm I thought it pretty clearly explained how you could create bulk events. Paired with some sheets formulas the methods shown here could be very powerful. Mind you it did not go over updating, although I would think you could pair it with the deleteEvent() method to effectively do the same thing

[@ПавелПитерский-д7г]

@@derekherzog1569 Yes, it's explained how one could create bulk of events from sheet, but my thoughts was (based on video title) that you find the way how to use calendar API to create events in batch/bulk )) Similar to Batch update operations in sheets API The matter is that if you have a really lot of events to create (as I do) - creating them "one by one" calling the API in "for" cycle takes a lot of time (since individual API calls are slow) and in my case I have to make it in few iterations or script will be failed due to execution's overtime

[@derekherzog1569]

@@ПавелПитерский-д7г Thanks for explaining, that makes sense. Based on your issue though I'm worried I"m soon going to be encountering the same issue. I'm also working on a project where I have to programmatically create/update bulk calendar events. I don't think I've ever had to go over 100 yet though and I haven't yet encountered the error you're getting (fingers crossed)

[@GaneshMahadevSirsat]

gettting error Exception: The parameters (String,String,String) don't match the method signature for CalendarApp.Calendar.createEvent.

[@pichit.raetai]

thank you