How to pull and reference data from a given cell in a Google spreadsheet?

Question

I am working on a tool for my department that, in the end, will look at people's google calendars over a set date range and populate a spreadsheet with a list of all the scheduled meetings they had during that date range.

The code currently works with a hard-coded date and email to draw from, but gives the above error when I have it reference a spreadsheet cell (people will be inputting their email address and date range to draw from into a fillable form).

I have tried using getRange(), getValue(), and getCell(), and none of them have worked.

The problem code is as such:

var sheet = SpreadsheetApp.getActive();

var inputSheet = sheet.getSheetByName("InputCleaner");

var outputSheet = sheet.getSheetByName("RawData");

var mycal = inputSheet.getCell(5,2,1,1);

var cal = CalendarApp.getCalendarById(mycal);

var events = cal.getEvents(new Date("January 12, 2014 00:00:00 CST"), new Date("July 18, 2019 23:59:59 CST"), {search: '-project123'});

When I attempt to execute the code above, it gives the following error:

TypeError: Cannot find function getCell in object Sheet. (line 32, file "Code")

The code above functions perfectly if line 3, instead of referencing a cell, just has the email address itself like so:

var mycal = "[email protected]";

But I need it to be able to draw the email address from a specific cell in the sheet.

Cooper Cooper · Accepted Answer · 2019-06-05T16:31:43

try this:

function function1() {
  var ss=SpreadsheetApp.getActive();
  var ish=ss.getSheetByName("InputCleaner");
  var osh=ss.getSheetByName("RawData");
  var mycal=ish.getRange(5,2).getValue();
  var cal=CalendarApp.getCalendarById(mycal);
  var events=cal.getEvents(new Date("January 12, 2014 00:00:00 CST"), new Date("July 18, 2019 23:59:59 CST"), {search: '-project123'});//not sure about this line
}

Here's a function to display your calendar Id's:

function displayCalendarInfo()
{
  var cals=CalendarApp.getAllCalendars();
  var s='Calendar Information';
  for(var i=0;i<cals.length;i++)
  {
    s+=Utilities.formatString('<br />Name: %s Id: %s', cals[i].getName(),cals[i].getId());
  }
  s+='<br /><input type="button" value="Close" onClick="google.script.host.close();" />';
  var ui=HtmlService.createHtmlOutput(s).setWidth(1200).setHeight(450);
  SpreadsheetApp.getUi().showModelessDialog(ui, 'Calendar Data');
}

How to pull and reference data from a given cell in a Google spreadsheet?

1 Answers