CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-calendar-api
Mauricio Alvarez
Mauricio Alvarez

Reputation: 61

Can I set a variable to be an "Id" for a .getCalendarById in google script?

So I want a google spreadsheet to take data from a calendar with two tabs. One tab will be where the data is inputted; "ss.". The second tab will be a settings tab where you can select the DateFrom, DateTo and CurrentEmail; "ssSettings". When the user enters the dates to and from, they can also see what email the script is currently getting its information from and they can change it if they want. I set the variable for "datefrom" and "dateto" but when I try to do the same for the "currentEmail" i get the following error;

TypeError: Cannot call method "getEvents" of null. (line 8, file "Code")

anyone know how to solve this? Im fairly new to google script. This is my script;

    function getEvents() {
      var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("GettingEvents")
      var ssSettings = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("GettingEventsSettings");
      var DateFrom = ssSettings.getRange("B1").getValue();
      var DateTo = ssSettings.getRange("B2").getValue();
      var CurrentEmail = ssSettings.getRange("B3").getValue();
      var cal = CalendarApp.getCalendarById(CurrentEmail);
      var events = cal.getEvents(DateFrom, DateTo);
      var lr = ss.getLastRow();
      ss.getRange(2, 1, lr, 5).clearContent();
      for(var i = 0;i<events.length;i++){
         var title = events[i].getTitle();
         var sd = events[i].getStartTime();
         var ed = events[i].getEndTime();
         var loc = events[i].getLocation();
         var des = events[i].getDescription();

         ss.getRange(i+2,1).setValue(title);
         ss.getRange(i+2,2).setValue(sd);
         ss.getRange(i+2,3).setValue(ed);
         ss.getRange(i+2,4).setValue(loc);
         ss.getRange(i+2,5).setValue(des);
      }
    }

I'd like the settings tab to look like this, or something that can be edited by the user without screwing with the code.

enter image description here

Upvotes: 1

Views: 211

Answers (2)

ale13
ale13

Reputation: 6052

The reason you are getting the TypeError: Cannot call method "getEvents" of null. (line 8, file "Code") is because the script you are using is not bound to the active spreadsheet. The .getActive() method returns the currently active spreadsheet, or null if there is none, which happened in your case in the code provided above.

In order to be able to interact with the Spreadsheet, you have to update your code to this:

var as = SpreadsheetApp.openById("YOUR_SPREADSHEET_ID");
var ss = as.getSheetByName("GettingEvents")
var ssSettings = as.getSheetByName("GettingEventsSettings");

The .openById() method opens the Spreadsheet with the specified id and therefore you can get the sheets by their name.

Furthermore, I suggest you take a look at these links since they might be of help in your future development:

  1. Class SpreadsheetApp - openById();

  2. Class SpreadsheetApp - getActive().

Upvotes: 1

Cooper
Cooper

Reputation: 64032

You can use something like this get the info on all of your calendars:

function calinfo() {
  var cals=CalendarApp.getAllCalendars();
  var calObj={nameA:[]};
  cals.forEach(function(cal,i){calObj[cal.getName()]=cal.getId();calObj.nameA.push(cal.getName());});
  Logger.log(JSON.stringify(calObj));
}

Upvotes: 0

Related Questions