Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>This is very similar to a question asked just two days ago, which was about synchronizing a spreadsheet of events with a calendar. It sounds like you want to consider the spreadsheet to be the master of events that it originates, which would simplify the problem considerably. The basics of what you need to do are covered in <a href="https://stackoverflow.com/a/15746141/1677912">this answer</a>. If you'd rather just modify existing code, I've got an implementation below.</p> <p>I have a modified version of the code from <a href="http://blog.ouseful.info/2010/03/04/maintaining-google-calendars-from-a-google-spreadsheet/" rel="noreferrer">this blog</a>, that will modify pre-existing calendar entries to match the info in the spreadsheet. I have arranged my spreadsheet differently, and this is reflected in the code.</p> <blockquote> <p>Date | Title | Start Time | End Time | Location | Description | <strong><em>EventID</em></strong></p> </blockquote> <p>The event ID column gets filled in by the script when new events are created, and is then used in later invocations to retrieve events from the calendar, thereby avoiding duplication.</p> <h2>Script</h2> <pre class="lang-js prettyprint-override"><code>/** * Adds a custom menu to the active spreadsheet, containing a single menu item * for invoking the exportEvents() function. * The onOpen() function, when defined, is automatically invoked whenever the * spreadsheet is opened. * For more information on using the Spreadsheet API, see * https://developers.google.com/apps-script/service_spreadsheet */ function onOpen() { var sheet = SpreadsheetApp.getActiveSpreadsheet(); var entries = [{ name : "Export Events", functionName : "exportEvents" }]; sheet.addMenu("Calendar Actions", entries); }; /** * Export events from spreadsheet to calendar */ function exportEvents() { var sheet = SpreadsheetApp.getActiveSheet(); var headerRows = 1; // Number of rows of header info (to skip) var range = sheet.getDataRange(); var data = range.getValues(); var calId = "YOUR_CALENDAR_ID"; var cal = CalendarApp.getCalendarById(calId); for (i=0; i&lt;data.length; i++) { if (i &lt; headerRows) continue; // Skip header row(s) var row = data[i]; var date = new Date(row[0]); // First column var title = row[1]; // Second column var tstart = new Date(row[2]); tstart.setDate(date.getDate()); tstart.setMonth(date.getMonth()); tstart.setYear(date.getYear()); var tstop = new Date(row[3]); tstop.setDate(date.getDate()); tstop.setMonth(date.getMonth()); tstop.setYear(date.getYear()); var loc = row[4]; var desc = row[5]; var id = row[6]; // Sixth column == eventId // Check if event already exists, update it if it does try { var event = cal.getEventSeriesById(id); } catch (e) { // do nothing - we just want to avoid the exception when event doesn't exist } if (!event) { //cal.createEvent(title, new Date("March 3, 2010 08:00:00"), new Date("March 3, 2010 09:00:00"), {description:desc,location:loc}); var newEvent = cal.createEvent(title, tstart, tstop, {description:desc,location:loc}).getId(); row[6] = newEvent; // Update the data array with event ID } else { event.setTitle(title); event.setDescription(desc); event.setLocation(loc); // event.setTime(tstart, tstop); // cannot setTime on eventSeries. // ... but we CAN set recurrence! var recurrence = CalendarApp.newRecurrence().addDailyRule().times(1); event.setRecurrence(recurrence, tstart, tstop); } debugger; } // Record all event IDs to spreadsheet range.setValues(data); } </code></pre> <h2>Delete / Recreate</h2> <p>In this alternative, the eventID is used to find and delete the previously existing event. After that, a new event is created with the data in the spreadsheet. This has the benefit that all values of the event can be updated, including start and stop times (see Notes below). On the other hand, any changes that were made to the original event will be lost - for instance, if other people had been invited to the event, or custom reminders were added.</p> <p>To use this alternative, simply replace the matching code with this:</p> <pre class="lang-js prettyprint-override"><code>// Check if event already exists, delete it if it does try { var event = cal.getEventSeriesById(id); event.deleteEventSeries(); row[6] = ''; // Remove event ID } catch (e) { // do nothing - we just want to avoid the exception when event doesn't exist } //cal.createEvent(title, new Date("March 3, 2010 08:00:00"), new Date("March 3, 2010 09:00:00"), {description:desc,location:loc}); var newEvent = cal.createEvent(title, tstart, tstop, {description:desc,location:loc}).getId(); row[6] = newEvent; // Update the data array with event ID debugger; </code></pre> <h3>Notes</h3> <ul> <li>The Documentation for <code>getEventSeriesById</code> wrongly states it returns <code>null</code> when no matching event is found, when instead it throws an exception. (nasty!) So I've enclosed it in a try / catch block just to keep on swimming.</li> <li>Unfortunately, while <code>getEventSeriesById</code> works to retrieve an event, it <code>returns</code> an <code>EventSeries</code> object, which does not support the <code>setTime()</code> method. If you don't expect to change the time of events, this OK. Otherwise, you can change the <code>Event</code> into an <code>EventSeries</code> by setting the <em>recurrence</em> rules &amp; times, or delete the old event and create a new one, as shown in <strong>Delete / Recreate</strong>. <a href="http://code.google.com/p/google-apps-script-issues/issues/detail?id=1154" rel="noreferrer">Issue 1154</a>. </li> <li>The spreadsheet always wins. Any event changes (in relevant fields) recorded via the Google Calendar will be overwritten by the script.</li> </ul>
    singulars
    1. This table or related slice is empty.
    1. POCreate Google Calendar Events from Spreadsheet but prevent duplicates
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USMogsdad
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIt seems this is a subject you really like :-) +1 for being exhaustive.
      singulars
      1. PO
      1. USSerge insas
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload