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

Question

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.

标签： google-apps-scriptgoogle-sheetsgoogle-calendar-api

Answer 1

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().