Forgive me, I'm new to all of this. I only have a very rudimentary grasp of programming in general (early concepts of Python/C, but nothing extensive); however, I do have an extensive background in formal and symbolic logics, which generally allows me enough skill to stumble around such basic needs until I figure something out.
But I have obviously met my match here, since I have been going insane over the last twenty four hours trying to understand what the hell I am doing wrong. I've searched, and searched, and SEARCHED, but every link and Q/A I could find on the subject seemed to be a different issue, OR the right issue, but it is from eight years ago and the solution has changed (such as click on this menu and do 'x', except that menu doesn't exist anymore).
Hardward/Software Involved: I am using my work computer which is a MacBook Air M1 2020, with Ventura 13.1 (this shouldn't actually impact my code, but since I am absolutely NOT a Mac person, adjusting to the most basic of hotkeys has been infuriating enough that I often fantasize the thing "slipping" out my backseat window on my commute to work as I crossover the Mississippi River).
I also use Chrome as my browser (version 109 something or other. Up to date as of posting).
I am attempting to write this script for a function via Google Apps Scripts.
Intended Goal: I am just trying to create a simple (ha!) function that will take information from a Google Spreadsheet (my own) and use that data to create a new Event on Google Calendar (also my own).
Basically, I started a new job where 6 or so different people are adding events and meetings to a single shared calendar, and every, single, god**ned one of them feels the need to use a different template, language, verbage, and even order whenever they make the event. This is leading to our calendar being a nightmare for figuring ANYTHING out.
My hope is that I can create a Google Form that is all check boxes and drop down options, that anytime anyone needs to create an event, they just use that single form. That form will then compile the results in my Google Sheet, which will then be able to create an event that is UNIFORM and CONSISTENT with all other events on our calendar.
What I've Done So Far: I have read a dozen or so quick guides, and seen another two dozen YT videos on how to do this, and even watching them, I CANNOT replicate what is going on.
I THINK my issue is coming from some sort of API discrepancy, but I swear I have tried everything any site I could find would suggest, and nothing has even come close to resolving my issue.
I have added both Sheets AND Calendars APIs to my "services" tab on the left. I also even created a Google Developers Account and added both APIs there, although this is where I feel like I am missing something.
I felt like I had to do WAAAAY too much work to enable APIs via Google Developers. In fact, I had to go as far as create an OAuth as if my tiny function were a full App I wanted to release into the Play Store. That can't actually be the steps I was supposed to take.
CODE
function climbevent() {
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lr = ss.getLastRow();
var cal = Calendarapp.getcalendarbyid("random#[email protected]")
var data = ss.getRange("B7:K"+ lr).getValues()
for(var i=0;i<data.length;i++){
cal.createevent(data[i][0], data[i][5], data[i][6],{location: data[i][9], description: data[i][7],guests: data[i][8]})
}
}
(Note: Only thing above that I've altered is where it says [random#s]. The rest is as is)
Error Code
ReferenceError: Calendarapp is not defined climbevent @ Event Maker.gs:5
Question
WHY THE HELL IS MY CALENDAR NOT DEFINED WHEN I HAVE MY GOD FORSAKEN GOOGLE CALENDAR ID RIGHT THERE!?!??! Just to be extra safe, I made my calendar public (This whole process is just a test so I can figure it out before I do the real thing on our work Google Account), so anyone with the link to my Calendar ID should be able to see it. In theory this should AT LEAST allow for the code to REFERENCE the calendar. I could see me needing more permissions to edit, but the reference code is RIGHT THERE.
What am I doing wrong? What am I missing? Feel free to ELI5, I won't take offense.
Please send help (and if you ever look out and see a MacBook floating on down the Mississippi some day, you never saw this post).
Potential Later Problems: I have already worked out how to layout my Sheet to get the actual information I need, and the format it needs to be delivered in (since the Form can only do so much). This shouldn't be an issue, but the law of attempting to brute force anything like this means I will likely have problems here later. However, I feel I can handle those.
Automation. I'd LOVE for this to be an automated action (someone submits a form, and voila, an event appears!). I think that might be a Pipedream. But hopefully with one of the numerous resources we use at work (e.g. Monday.com, Zappier, etc.), I'll get lucky, so I'm not ready to let go of this dream just yet. But first I need to make the function work manually before I can dream of automation.
Editing events. I'm sure the editing of, and re-submitting of events will be a future downfall of this system, since it will likely either miss items, or duplicate them. I am willing to take the time and create some If/Then systems that will keep at least some of these things at bay. But again, Future Me can figure that out.
I don't have enough reputation :) to comment. But maybe the origin of everything is because Javascript is a case sensitive language? ;D
Try changing:
var cal = CalendarApp.getCalendarById("random#[email protected]")
...
cal.createEvent(data[i][0], data[i][5], data[i][6],{location: data[i][9], description: data[i][7],guests: data[i][8]})