[GAS]Creation of the data for ManyTime app from Google calendar

In this article, you can make the following process by using Google Apps Script.

1:Extract the events in a given period from Google Calendar, write to Google
Spreadsheet.
2:By getting data from Google Spreadsheet above, write ManyTime data to
Google Drive.

(A) About scripts
You can create a document in Google Spreadsheet, and then set the script
below there. Because there are differences in the data that is entered
or your account, you may need to customize the script. I write the following
additional information.

The main customization point:

fromDayToDay
1:The start date of the period specified
2:The end date of the period specified
3:Waiting time - It is set to 10 seconds.
4:Output file name of ManyTime data.

writeNewSheetFromToOn
5:Spreadsheet key of Google Spreadsheet
6:Calendar ID of Google Calendar
7:Regional specification - Asia / Tokyo
8:Offset hours

writeManyTimeFileFromTo
9:Newline character - settings \n, for Mac use.


2:The end date of the period specified
In the script, the end variable is a Date object that has the period specified end
date. For example, if the end variable is the Date object of '2013/12/11',
the end time of the period specified will be '2013-12-10 23:59:59' before
'2013-12-11 00:00:00'.
If you want to get events between 12/09 and 12/10, specify that the start day is
'2013/12/09' and the end day is '2013/12/11'.

5:How to get the spreadsheet key of Google Spreadsheet:
Open the Google Spreadsheet document that you created in Google Drive,and
make sure the URL. Although it is a URL like
https://docs.google.com/spreadsheet/ccc?key=...&usp = ...,
in this, the subsequent part of 'key=' is the spreadsheet key.

6:How to get Calendar ID of Google Calendar:
In 'My calendars' at the left of Google Calendar, ex. in 'Working',
select 'Calendar Settings' item from the arrow button menu of the calendar
that you want to. Get Calendar ID from Calendar Address of 'Working' Details
after opening the page. The script　below uses only one calendar.

7:Regional specification - Asia/Tokyo
If you does not live in Asia/Tokyo region,
select "File>New>Script file" in Script editor, and create a new script as
following.

  function logTimeZone() {
    var timeZone = Session.getTimeZone();
    Logger.log(timeZone);
  }

Then, run the script, select "View>Logs" and check the dialog.

https://developers.google.com/apps-script/reference/base/session

8:hourOffset veriable
In the script below, all-day events have the length of one hour.
The hourOffset specifies the start time of the first all-day event.
It will be 0:00:00 if you specify 0, and it will be 13:00:00 if you specify 13.
For example, as there are all-day event #1 and all-day event #2,
if you specify the hourOffset to 0, event #1 will be between 00:00:00
and 00:59:00, and event #2 will be between 01:00:00 and 01:59:00.

Events of Google Calendar:
Google Calendar events to be handled, these are assumed events with
no overlap in time.
All-day events are one hour long, they will sort in the order after
the hourOffset.
It should be noted, can not be handled in ManyTime when normal events
and all-day events overlap.
Multi-day events will be all-day events that have the start date in the period.
Multi-day events do not handle if the start date is not included in the period.

Code that was helpful in the script:
http://stackoverflow.com/questions/14415752/google-calendar-app-script
http://micomura.hatenablog.jp/entry/2012/01/30/180803
http://d4-1977.hatenablog.com/entry/20110116/1295140259

(B)How to use
1:Create the document of Google Spreadsheet.
2:Open the script editor from "Tools>Script editor...".
3:Select "File>New>Script file", and create a new script.
4:Paste the code below fromDaytoDay.gs to replace the script.
5:Select fromDaytoDay from "Select function" menu.
6:Press the Run button. Accept the warning dialog at run time depending on
your circumstances.
7:Make sure the contents of manytime-4.txt on Google Drive.
8:Copy the contents of manytime-4.txt file to Dropbox, and make sure the data
with ManyTime app.
9:Delete the sheet that is generated by the script as necessary.

fromDayToDay.gs:

//Extract Google Calendar events of the specified period, 
//you can create ManyTime data into Google Spreadsheet, and 
//export it to manytime-4.txt file.

function fromDayToDay() {
  var start = new Date('2013/12/09'); //1:The start date of the period specified
  var end = new Date( '2013/12/11' ); //2:The end date of the period specified
  var sheetName = Utilities.formatDate(start,"Asia/Tokyo", "yyyy-MM-dd"); //7:Regional specification
  writeNewSheetFromToOn(start, end, sheetName);
  //3:Waiting time - It is set to 10 seconds.
  Utilities.sleep(10*1000);
  var csvFileName = 'manytime-4.txt'; //4:Output file name of ManyTime data.
  writeManyTimeFileFromTo(sheetName, csvFileName);
}

//Write to the sheet with the name of the argument aSheetName, the events between 
//the aStart arguments and anEnd.
//Hard-coded Calendar ID and Google Spreadsheet key.

function writeNewSheetFromToOn(aStart, anEnd, aSheetName){

  var ss = SpreadsheetApp.openById( 'Spreadsheet key' ), //5:Spreadsheet key of Google Spreadsheet
      sheet = ss.insertSheet(aSheetName),
      cals = ['Calendar ID'], c, cal, calName, //6:Calendar ID of Google Calendar
      events, i, details,
      eventslog = [], e,
      rows = [], range,
      anArray, startTime, eventTime, endTime,
      hourOffset, eventNumberInDay, aStartDay, previousStartDay,
      anEndDay, aRegionalSpec = "Asia/Tokyo"; //7:Regional specification

  //Each to be eligible if you have more than one calendar. The only one here.
  for (c = 0; c < cals.length; c += 1) {

    cal = CalendarApp.getCalendarById(cals[c]);
    calName = cal.getTitle();
    //Get the events between aStart and anEnd.
    events = cal.getEvents(aStart, anEnd);
    events = events.reverse(); //Reverse order.
    
    //From events, eliminate all-day events of the previous argument aStart.
    anArray = new Array();
    startTime = Utilities.formatDate(aStart,aRegionalSpec, "yyyyMMdd");
    for (i = 0; i < events.length; i++) {
      if (events[i].isAllDayEvent()) {
        eventTime = Utilities.formatDate(events[i].getStartTime(),aRegionalSpec, "yyyyMMdd");
        //All-day events
        if (eventTime < startTime) {
          //The start date of all-day event is before the start date of the argument aStart.
          continue;
        }
      }
      //It is all-day event of argument aStart later, or usual events of aStart later.
      anArray.push(events[i]);
    }
    events = anArray;
    //From events, eliminate all-day events of the argument anEnd later.
    anArray = new Array();
    //Get the start date of the argument anEnd.
    endTime = Utilities.formatDate(anEnd,aRegionalSpec, "yyyyMMdd");
    for (i = 0; i < events.length; i++) {
      if (events[i].isAllDayEvent()) {
        //Get the start date of the event.
        eventTime = Utilities.formatDate(events[i].getStartTime(),aRegionalSpec, "yyyyMMdd");
        //All-day events
        if (endTime <= eventTime) {
          //Start date of all-day event is the argument anEnd later.
          continue;
        }
      }
      //All-day events of the previous days of anEnd, 
      //or usually events of the previous day of anEnd.
      anArray.push(events[i]);
    }
    //Processing of events
    events = anArray;
    
    //Add events of the current calendar to the array of all events.
    hourOffset = 0; //8:Offset hours, 0 o'clock - 23 o'clock 
    eventNumberInDay = hourOffset - 1; //-1 + hourOffset
    eventslog = eventslog.concat(
      events.map(function(event) {
        if (event.isAllDayEvent()) {
          //All-day event
          aStartDay = Utilities.formatDate(event.getStartTime(),aRegionalSpec, "yyyyMMdd");
          if (aStartDay == previousStartDay) {
            eventNumberInDay = eventNumberInDay + 1;
          } else {
            eventNumberInDay = hourOffset; //0 + hourOffset
          }
          previousStartDay = aStartDay;
          //Calculation of the end time
          var milliSecStart = event.getStartTime().getTime();
          var newStartTime = new Date(milliSecStart + eventNumberInDay*3600*1000);
          var newEndTime = new Date(milliSecStart + (eventNumberInDay + 1)*3600*1000 - 60*1000);
          //Multi-day events support
          var milliSec = event.getEndTime().getTime();
          var anotherEndTime = new Date(milliSec - 60*1000);
          anEndDay = Utilities.formatDate(anotherEndTime,aRegionalSpec, "yyyyMMdd"); 
          var endDateString = "";
          if (aStartDay != anEndDay) {
            //Put the ending date in a comment.
            endDateString = Utilities.formatDate(anotherEndTime,aRegionalSpec, "yyyy-MM-dd"); 
            endDateString = "(~" + endDateString + ")";
          }
          //
          return {
            time: new Date(event.getStartTime()).getTime(), // sort by this
            details: [
              Utilities.formatDate(newStartTime,aRegionalSpec, "yyyy-MM-dd HH:mm:ss@"),
              Utilities.formatDate(newEndTime,aRegionalSpec, "yyyy-MM-dd HH:mm:ss@"),
              event.getTitle() + endDateString,
              eventNumberInDay
            ]
          };
        } else {
          //Usually event
          var milliSec = event.getEndTime().getTime();
          var newEndTime = new Date(milliSec - 60*1000);
          return {
            time: new Date(event.getStartTime()).getTime(), // sort by this
            details: [
              Utilities.formatDate(event.getStartTime(),aRegionalSpec, "yyyy-MM-dd HH:mm:ss@"),
              Utilities.formatDate(newEndTime,aRegionalSpec, "yyyy-MM-dd HH:mm:ss@"),
              event.getTitle(),
              eventNumberInDay
            ]
          };
        }
      })
    );
  }

  // Sort array of event so date order can be either way by reversing a & b
  eventslog.sort(function(a, b) { return a.time - b.time; });

  rows = eventslog.map(function(entry) { return entry.details; });
  //If thhe number of rows is 0, it will be an error.
  if (rows.length > 0) {
    range = sheet.getRange(1, 1, rows.length, 4);
    range.setValues(rows);
  }
}

//Write ManyTime data to the file argument aCsvFileName from the sheet 
//with the name of the argument aSheetName.
function writeManyTimeFileFromTo(aSheetName, aCsvFileName) {

  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(aSheetName);
  var maxColumn = ss.getLastColumn() - 1; //Correction: copy 3 columns from 4 columns.
  var maxRow    = ss.getLastRow();
  //If the sheet has no data, return from the function.
  if (maxColumn < 1 && (maxRow < 1)) {
    return;
  }
  //
  var data = ss.getRange(1,1,maxRow,maxColumn).getValues();
  var csvFile = "";
  //Create a content export to extract data from the sheet.
  if (data.length > 0) {
    var csv = "";
    for (var row = 0; row < data.length; row++) {
      for (var col = 0; col < data[row].length; col++) {
          //For example, remove the trailing @ from "2013-01-08 00:00:00 @". 
        if (data[row][col].toString().indexOf("@") != -1) {
          data[row][col] = data[row][col].replace(/@/, "");
        }
      }

      csv += data[row].join("\t") + "\n"; //9:Newline character
    }
    csvFile = csv;
  }
  //Get the file of argument aCsvFileName
  var files = DocsList.getAllFiles();
  var file;
  for (var i in files) {
    if (files[i].getName() == aCsvFileName) {
      file = files[i];
      break; 
    }
  }
  //The variable file if it exists, add lines of ManyTime data to the end as the existing file.
  //If it does not exist, create a new file with the contents of the aCsvFileName argument.
  if (file) {
    file.append(csvFile);
  } else {
    var dataBlob;
    dataBlob = Utilities.newBlob(csvFile, 'text/plain', aCsvFileName);
    DocsList.createFile(dataBlob);
  }
}