google-apps-script,google-spreadsheet,google-calendar , Google Apps Script: event.setTime error and time format


Google Apps Script: event.setTime error and time format

Question:

Tag: google-apps-script,google-spreadsheet,google-calendar

I'm creating something of a booking system which is nearly complete. Currently I'm collecting data from a form, passing it to a sheet, and then using some of that info to create a calendar event. This all works fine.

On creating the event, I'm also collecting the eventID so that I can use it to identify the event and update it from the spreadsheet. These updates are also working, with the exception of updating the start/end date and time which causes the following error:

TypeError: Cannot find function setTime in object CalendarEventSeries.

This is the code I'm working with:

var eventStart =  sh.getRange("D"+rowNumber).getValues();
var eventEnd =  sh.getRange("E"+rowNumber).getValues();
event.setTime(eventStart, eventEnd);

I'm doing exactly the same thing with setLocation and setTitle without a problem.

I'm new to this, I don't know what an object is and so the error message means very little to me! But I can see that setTime is a method outlined in the class 'CalendarEvent' (https://developers.google.com/apps-script/reference/calendar/calendar-event#setTime(Date,Date)), but not in 'CalendarEventSeries'. All my events are on-off events anyway?

Thanks in advance for any pointers.

UPDATE

I have integrated Mogsdad's Advanced Calendar Service code, and after

"var endTime = parseDate(event.end.date||event.end.dateTime);"

I am checking/logging 'startTime' and 'event'. 'startTime' is coming back as 'invalid date' (bad thing?) and 'event' is coming back with all the calendar entry info I can imagine (good thing I hope?!).

Where should the parseDate function actually go? Maybe I have it in the wrong place (I've tried it all over the place!) and this isn't being used?

Also, now the event I want to edit has been identified, are the dates parsed and used to search for the event I have already found in order to return a Calendar event that I can ultimately use setTime on? Is that the whole point of this?

Thanks for bearing with me.

UPDATE 2 - INVALID DATE?

If I skip the parsing and log the variable like this:

var startTime = event.start.dateTime;

The result is 2015-05-24T02:00:00+01:00 which I think is spot on. So the invalid date is definitely a case of something going wrong during the parse function as it only then that it returns 'invalid date'.

Code in context below.

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [{name: "Create Event Document Manually", functionName: "addSheet"},{name: "Update Calendar Entry", functionName: "getEventById"}
  ];


  ss.addMenu("Select Row & Click Here", menuEntries);
}



/**
 * Retrieve a CalendarApp CalendarEvent object from IDs.
 * This version utilizes the Advanced Calendar Service, which must be
 * enabled before use.
 *
 * @param {string} calendarId   ID of calendar to be searched
 * @param {string} eventId      Event ID to match
 *
 * @returns {CalendarEvent}     CalendarApp CalendarEvent object or null
 */


function getEventById(calendarId, eventId) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var rowNumber = sh.getActiveRange().getRowIndex();
  var myEventId = "q8q533oioluipirksmno88qv2g";
  var calendarId = "[email protected]";


  // Get event by ID.
  var event = Calendar.Events.get(calendarId, myEventId);
  // This isn't a CalendarApp CalendarEvent though, so use the info
  // in the event to find it AGAIN as the right type.




  // Get start & end times of event. All-day events start at start.date,
  // while other events start at start.datetime. Likewise for end.

  var startTime = parseDate(event.start.date||event.start.dateTime);
  var endTime = parseDate(event.end.date||event.end.dateTime);



   Logger.log('Variables: ' + startTime + ' and ' + endTime);

  // Get array of events that fall between startTime & endTime
  var calEvents = CalendarApp.getEvents(startTime, endTime);

  // Search through those events, looking for a match by ID
  for (var i=0; i<calEvents.length; i++) {
    var curId = calEvents[i].getId().split('@')[0];  // extract id from [email protected]
    if (curId == eventId) {
      // Mission accomplished; we have an Event object with given id!
      return calEvents[i];

    }
  }
  // We did not find matching event
  return null;


}




  function parseDate(string) {
  var parts = string.split('T');
  parts[0] = parts[0].replace(/-/g, '/');
  return new Date(parts.join(' '));
}

Answer:

Serge has it right - the problem is that you've retrieved a CalendarEventSeries object, not a CalendarEvent. Since the only method in the service that will look for an event by ID is getEventSeriesById(iCalId), you're kinda stuck.

One option is to use the Advanced Calendar Service instead:

var event = Calendar.Events.get(calendarId, eventId);

For new code, that's a great option, especially for developers already used to Javascript APIs. If you're a beginner or not familiar with the Advanced Services, though, you'll find that there's a steeper learning curve than for the Calendar Service.

In that case, these utilities should help you stick with the CalendarApp and its Classes and Methods, by filling the need for a getEventById() function.

Advanced Calendar Service under the hood

There are two versions of getEventById(). This first one utilizes the Advanced Calendar Service, which must be enabled before use. The code is fairly straight-forward. You must provide the Calendar ID explicitly, since this isn't a Class Method. Example:

var calendarId = CalendarApp.getDefaultCalendar().getId();
var eventId = "smmd8h1dfe9lo9bip52hidnqk0";
var event = getEventById(calendarId, eventId);

Code:

/**
 * Retrieve a CalendarApp CalendarEvent object from IDs.
 * This version utilizes the Advanced Calendar Service, which must be
 * enabled before use.
 *
 * @param {string} calendarId   ID of calendar to be searched
 * @param {string} eventId      Event ID to match
 *
 * @returns {CalendarEvent}     CalendarApp CalendarEvent object or null
 */
function getEventById(calendarId, eventId) {
  // Get event by ID.
  var event = Calendar.Events.get(calendarId, eventId);
  // This isn't a CalendarApp CalendarEvent though, so use the info
  // in the event to find it AGAIN as the right type.

  // Get start & end times of event. All-day events start at start.date,
  // while other events start at start.datetime. Likewise for end.
  var startTime = parseDate(event.start.date||event.start.dateTime);
  var endTime = parseDate(event.end.date||event.end.dateTime);

  // Get array of events that fall between startTime & endTime
  var calEvents = CalendarApp.getEvents(startTime, endTime);

  // Search through those events, looking for a match by ID
  for (var i=0; i<calEvents.length; i++) {
    var curId = calEvents[i].getId().split('@')[0];  // extract id from [email protected]
    if (curId == eventId) {
      // Mission accomplished; we have an Event object with given id!
      return calEvents[i];
    }
  }
  // We did not find matching event
  return null;
}

Calendar API via UrlFetchApp

This version utilizes the Calendar API via UrlFetchApp, which doesn't require any special enablement. However, the code is more complex than the previous version.

/**
 * Retrieve a CalendarApp CalendarEvent object from IDs.
 * This version utilizes the Calendar API via UrlFetchApp, so
 * requires no enablement. However, it's more complex.
 *
 * @param {string} calendarId   ID of calendar to be searched
 * @param {string} eventId      Event ID to match
 *
 * @returns {CalendarEvent}     CalendarApp CalendarEvent object or null
 */
function getEventById(calendarId, eventId) {
  // Prepare a GET request to API URL, to Get event by ID.
  var url = "https://www.googleapis.com/calendar/v3/calendars/calendarId/events/eventId"
            .replace("calendarId",calendarId)
            .replace("eventId",eventId);

  var options = {
    headers: {
      'Authorization': 'Bearer ' +  ScriptApp.getOAuthToken()
    }
  }

  // Send request
  var response = UrlFetchApp.fetch(url, options);
  var rc = response.getResponseCode();
  var text = response.getContentText();

  // If result code is 200OK, process response text
  if (rc == 200) {
    // The event is contained in the response text; parse it into an object
    var event = JSON.parse(text);
    // This isn't a CalendarApp CalendarEvent though, so use the info
    // in the event to find it AGAIN as the right type.

    // Get start & end times of event. All-day events start at start.date,
    // while other events start at start.datetime. Likewise for end.
    var startTime = parseDate(event.start.date||event.start.dateTime);
    var endTime = parseDate(event.end.date||event.end.dateTime);

    // Get array of events that fall between startTime & endTime
    var calEvents = CalendarApp.getEvents(startTime, endTime);

    // Search through those events, looking for a match by ID
    for (var i=0; i<calEvents.length; i++) {
      var curId = calEvents[i].getId().split('@')[0];  // extract id from [email protected]
      var desc = calEvents[i].getDescription();
      if (curId == eventId) {
        // Mission accomplished; we have an Event object with given id!
        return calEvents[i];
      }
    }
    // We did not find matching event
    return null;
  }
  else
    // An error in fetch, anything BUT 200
    throw new Error( ""+rc+JSON.parse(text).message );
}

Helper function

Both versions of getEventById() require this helper function, provided in Google's documentation.

/**
 * From https://developers.google.com/apps-script/advanced/calendar#listing_events
 *
 * Parses an RFC 3339 date or datetime string and returns a corresponding Date
 * object. This function is provided as a workaround until Apps Script properly
 * supports RFC 3339 dates. For more information, see
 * https://code.google.com/p/google-apps-script-issues/issues/detail?id=3860
 * @param {string} string The RFC 3339 string to parse.
 * @return {Date} The parsed date.
 */
function parseDate(string) {
  var parts = string.split('T');
  parts[0] = parts[0].replace(/-/g, '/');
  return new Date(parts.join(' '));
}

Related:


Automatically export incoming emails from Outlook to existing Google spreadsheet [closed]


email,outlook,google-spreadsheet,export
Is it possible at all to automatically export incoming emails from Outlook to existing Google spreadsheet (and be able to edit the spreadsheet, insert new data)? If it is so, where would you start? We want to manage our inbox this way and keep track of assignments. Thank you!...

Javascript (Google Scripts) value from a function is not returning


javascript,google-apps-script,google-spreadsheet,google-calendar
I think could be a simple one to solve, I am stuck working this simple issue out. I have called a createEvent function to create a google calendar event. As part of this function I also get the google calendar event ID as EventId and want to return it. For...

Google Sheets API for clickable images in Google Sheets


google-apps-script,google-spreadsheet,google-spreadsheet-api,google-spreadsheet-addons
In Google sheets, you can insert a clickable image to a cell which links to a function in app script. Can we also insert this clickable image and link it to app script from our server using Google Spreadsheet API? Or Can we call app script from our server? https://developers.google.com/apps-script/guides/menus#clickable_images_and_drawings_in_google_sheets...

How to get Google Sheet data into a HTML page using jQuery/Javascript?


javascript,jquery,html,google-spreadsheet,google-spreadsheet-api
Is there a way to obtain Google spreadsheets data into a HTML page using jQuery/Javascript? I couldn't find any examples/resources regarding the same on the Google sheets v3.0 API page. Could anyone help? I have tried to get the sheets feed using the following request GET https://spreadsheets.google.com/feeds/list/key/worksheetId/private/full...

Google Apps Script return array values and use them in a javascript function


javascript,arrays,google-apps-script
I am trying return an array and use it in a javascript function, but it doesn't seem to work. My Code.gs is as follows: function doGet() { return HtmlService.createHtmlOutputFromFile('test') .setSandboxMode(HtmlService.SandboxMode.IFRAME); } function test() { var locations = []; var ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/13q7pIeMUHll6_5xBUpBavaBqALt9fnFnOIO-Hwy_pFc/edit'), sheet = ss.getActiveSheet(), range = ss.getRange("D2:D4"), values =...

Prevent users from creating new sheets in a shared google spreadsheet


google-spreadsheet,google-docs
I have created a spreadsheet with all sheets protected from editing except for a single cell, where the user is supposed to enter a search value, which filters the sheet. I shared the spreadsheet with enabled editing to allow for that, but that also enables users to create new sheets,...

Google Form to Document Merge to Pdf to Mail


google-apps-script,google-spreadsheet
I think my issue is in the e.values I am using along with the ('keyXXXXX', XXXXX); that I am using. The original code I used was something I borrowed from TJ Houston's site. I am using it for an application process. I wrote the code following the process of using...

Search for value in variable Google Apps Script/JS


javascript,google-apps-script,google-calendar
Trying to determine if calendar events in Google Calendar were created by the user himself or if a CSV bulk upload was performed. If the script finds an ID with "CSV" somewhere in the value, then remove it. I tried to add an if statement where it looks at the...

Google Apps Script - Sharing outside of domain


google-apps-script,google-form
So I have followed the tutorial here, and everything works great... for me. google forms file upload complete example What I need to do is add the script to a Google Form, and allow users to upload an image along with the form. I know it will only put it...

Google sheet app script errors after laptop sleep


google-apps-script,google-spreadsheet
I have a custom function in a sheet that calls a mysql database. It extracts one value from mysql per row, e.g. | A | B -------------------------------- 1 | YES | =readCompleteFromMember(A1) 2 | NO | =readCompleteFromMember(A2) If I let my laptop go to sleep and open it the next...

Google Spreadsheet setBackgroundColors() “Deprecated”


google-apps-script,google-spreadsheet
A gs I have been using in the past to check for duplicates, is no longer working. The script would check all cells in the spread sheet if any were identical it would highlight all their occurrences green. I also had another function that would revert all the cells back...

Constantly getting an email: Cannot read property “0” from undefined


javascript,google-apps-script,gmail
I am constantly getting emails from "me" with this in it: Cannot read property "0" from undefined. I think it has something to do with Google Apps script because it started when I tried to code something. (for details: I was trying to code a "Contact Us Form" for my...

Script for Gmail label to Sheets


google-apps-script,gmail
I am trying to make a script that takes an email within a certain label and puts it into google sheets. Im using this function at the moment: function getMessagesWithLabel() { var destArray = new Array(); var threads = GmailApp.getUserLabelByName('Facebook').getThreads(0,10); for(var n in threads){ var msg = threads[n].getMessages(); var destArrayRow...

How to remove quote mark from array


javascript,google-apps-script
I have a script that will import data from a csv file in to a Google sheet. I would like to include the following to remove the quote marks from the data - .replace(/"/g, "") - but am not sure of the best place/syntax to add in to the existing...

How can I exclude a sheet in an array, and sort with a range


google-apps-script,google-spreadsheet
Good Morning everyone I have a code that was posted here a while back, I recently started using for a google spreadsheet and tweaked it a bit, how ever there are 2 thigs I have not been able to accomplish the first thing is to Exclude the Master sheet from...

Merging Multiple Arrays Evenly/Alternating with Javascript and Google AppScript


javascript,arrays,google-apps-script,merge
I'm trying to merge multiple arrays evenly/alternating in javascript/Google appScript. There are several arrays (5 or 6). I've tried 2 different methods, but neither worked. I don't work a lot with javascript honestly and I've managed to get the code to this point, but I can't get it merge properly;...

how i create a formula of payment using weeknum formula


google-spreadsheet,week-number
i need to calculate paymant of this people in the same week like you see in this spreadsheet https://docs.google.com/spreadsheets/d/1fHEtOFxZeRjJ07duCxxhUwUL_1G3BUrvKRMYjT9_OmM/edit#gid=91744412 I used WEEKNUM formula to extract the number of week using the data in column A ..for example "anselmi" earned 16 in week 24...... thanks

Access dropdown items in Google App Scripts UiApp


google-apps-script,listbox
I'm using the older depreciated UiApp class in javascript (because I have to) where I have a dropdown list I need to access the ListBox, and see all the values that it contains. Is there a way to do this? All the items must be present because there is method...

Copy Row if Sheet1 A contains part of Sheet2 C


rss,google-spreadsheet,google-docs,drive,advanced-search
So I'm trying to pull the data in a row from a separate sheet (sheet2!), if part of Col A has the the Date that is in sheet1! C1. Col A ex: "Build 251 at Fri Jun 12 03:03:49 2015" Col C1 ex: "Fri Jun 12" (Changes date every couple...

How to get the last entry containing a certain value in Google Spreadsheet


google-apps-script,google-spreadsheet,google-form,formulas
I have an answer spreadsheet linked to a Google form. I wish to get the last date that and answer was submitted. Lets say I my entries look like this : DATA : A B 1 Date String 2 2015-05-09 Abb 3 2015-05-11 Bcc 4 2015-05-12 Cdd 5 2015-05-20 Bcc...

Pass webapp link parameter to html file in Google Apps Script


google-apps-script
I am trying to pass a parameter from a webapp link, so it can be used to pre-fill an html form (using HtmlService). Apps Script - function doGet(e) { var formId = e.parameter.formId; return HtmlService.createTemplateFromFile('Index') .id = formId .evaluate() .setSandboxMode(HtmlService.SandboxMode.IFRAME) } Index.html - <form id="myForm"> <input type="text" name="formId" value="<?=id?>"><br> <input...

Break Google Apps HTMLService Page into multiple .html files


google-apps-script,google-apps
I want to combine a few related HTMLService interface in to a tabbed page. I can get the tabs working using the code here. But I want to put the page for each Tab into a different html file in the project. How would I show Billets.html inside the div...

Google Spreadsheet (Using named range in script)


google-apps-script,google-spreadsheet
Trying to get my script to use a named range. In that range I am trying to reset the cell colors back to white. I keep getting an object error, can't figure out how to make this work. function resetCheckDirectory() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var tableRange = ss.getRangeByName("rangename"); tableRange.setBackgrounds("#FFF");...

If Statement Not Working on Google Spreadsheets


google-spreadsheet
Can anyone explain why this returns false when I try to equate the two? The two are exactly the same. I'm basically trying to do a validation here and make sure the numbers match up. I want to check if cell B4 equals cell L2 on a different spreadsheet. The...

How to real-time monitor the emails?


api,google-apps-script,gmail,quota
Good day! I want to offer the implementation of google apps to my company. Also we have a need to reply on messages, that we recieve through our web-site in 10 minutes time limit. For this purpose our big team of sales managers a handling emails when they have a...

Get column to display month name Google App Script


google-apps-script
How do I set a column to display the name of the month instead of the whole date using the script? The function can manually be performed by selecting Format>Number>August in the menu but I want to do this in code. This is what I've tried but I can't seem...

Extract matching data from varying number of row


google-spreadsheet,spreadsheet,pivot-table
I am trying to build graphic indicators from a (potentially) large set of data using google-spreadsheet. So far, I've used a pivot table to extract the information from the raw data, and I want to build intermediate tables to calculate the different values I need for my indicators. So far,...

getJSON from Google Apps Script not working in Safari


google-apps-script,safari,getjson
I've created this piece of code to handle my user login. var gasUrl = "https://script.google.com/macros/s/[my id]/exec"; function submitFormData() { var username = $("#inputEmail").val(); var password = $("#inputPassword").val(); $.getJSON(gasUrl + "?username=" + username + "&password=" + password, function(data) { if(data[0]) { localStorage.setItem("username", data[0].username); localStorage.setItem("id", data[0].id); window.location.href = "/index.php"; } else {...

Script to check for email subject not in inbox runs without error but not as expected. Not sure what is wrong with syntax.


google-apps-script
I get an automated email when an event occurs, but need to be notified if it isn't received. It's easy to overlook something that doesn't happen. Here's what I have, but the var c remains 0 when script ends (I'm expecting it to not be 0 if email is found,...

Google spreadsheet script authorisation to BigQuery


google-apps-script,google-spreadsheet,google-bigquery
I have a Google spreadsheet with a script that connects to BigQuery (using this tutorial - https://developers.google.com/apps-script/advanced/bigquery?hl=ar-AE). It adds an extra menu option and users can run the script that executes a query to BigQuery. It works fine for me and I want to share this spreadsheet with other users...

Google Sheets Adds-on open automatically when user opens a sheet or clicks a link in worksheet


google-apps-script,google-spreadsheet
I created an google sheets add-on. However, it is a bit hidden in the adds-on menu. Is there a way to enable the Adds-on automatically when user opens a sheet or when user clicks a link in a sheet? I searched on Google Sheets documentation but found nothing. EDIT1: Since...

Convert from base 10 to base 3


google-spreadsheet,base
I have a whole column in Google sheets with the numbers 1-400. Is there an easy way to convert these numbers to base 3?

Output Google Spreadsheet to XML/RSS/Atom using Google AppScript Content Service


xml,google-apps-script,rss,google-spreadsheet,google-docs
So I've searched this site and found nothing really useful so far to accomplish this. I am wanting to turn a spreadsheet into an RSS feed using google script content service. How do I declare column values (i.e. pubDate, Author, Content) so it will output correct RSS/Atom feed. Following their...

Google Spreadsheet custom function to add csv in spreadsheet


google-apps-script,google-spreadsheet,google-spreadsheet-api
I am using Google spreadsheets, and in my google spreadsheet i want to import data from my RESTful web service. I cant use =IMPORTDATA due to the fact that i need to add a header to the url for auth purposes. This is the custom function i have so far:...

Increment count in column based on value in column


excel,google-spreadsheet,excel-formula,array-formulas
I've 2 columns A and B. A contains names and B contains the count of those names. Ex: let A1 have value "fruits". Then B1 must have value 1 because this is first time "fruits" is present in A. Next A2 has "flowers", and B2 will have 1 against "flowers"....

Counting values embedded in strings inside a column (Google Spreadsheets)


excel,google-spreadsheet,excel-formula,formula,countif
I have a Google Survey where I created some multiple choice questions. Now, I am trying to count the responses. [A] [B] [Response#] [Selections] [1] [Apple,Orange,Banana] [2] [Orange,Banana] [3] [Apple,Orange,Banana] [4] [Banana] [5] [Apple,Banana] [6] [Apple,Orange] . So on my summary spreadsheet, I would like to have the totals: [Favorite...

How to prevent Google Forms from converting form input to scientific notation format


javascript,google-apps-script,google-docs,google-form
I have a simple script set up that sends emails based on Google form entries using a script-based VLookup to get the contact emails. In some cases, Google Forms converts longer numbers entered into the form field to scientific notation. A workaround I have been using is to enter an...

Macro or other solution in excel to automate interaction of data in two worksheets to come up with a third worksheet


excel-vba,google-spreadsheet
I have no idea where to start with this problem (may be due to poor knowledge of terminology). I have two worksheets which I will put up in Google Sheets which I'd like to generate a third worksheet for (also in the same Google Sheets file). https://docs.google.com/spreadsheets/d/1ALQlQhNugUnQzM5NdbFsLX_TlhV6BzT_1TDQKc6hD5I/edit?usp=sharing I'd like to...