[GAS]Service of making the data for ManyTime app from Google calendar part1

Post:2015-03-14
Update:2015-04-28

In this article, modifying the script of "[GAS]Creation of the data for ManyTime
app from Google calendar", you can create a Web service to get the events that
took place the day before and update the ManyTime app data everyday.

In Part 1, I will write about how to start the GAS script trigger in time.
In Part 2, I will write about how to copy the data of ManyTime app
from Google Drive to Dropbox via Zapier.

The program of this article will perform the following processing.

1:Extract the events in the previous day from Google Calendar, and writes to Google Spreadsheet.
2:By getting data from Google Spreadsheet above, write ManyTime data to
Google Drive.
3:The processing of the above 1 and 2 perform once a day in the time
that specified by the trigger function of Google Spreadsheets. In addition,
the triggering of the trigger time in this article will be once in
between 5:00 am and 6:00 am.


Summarizing the setting in accordance with this article, the input and output
is as follows:

Input: The yesterday event that you have entered before 5:00 am today in
Google Calendar.

Output: Yesterday events processing is performed between 5:00 am and
6:00 am on today, ManyTime app data based on Google Calendar events of
yesterday is appended to the Temp-manytime-4.txt below the Google Drive.


(A) About scripts
You 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.

To customize the script, you will have to customize Calendar ID,
Spreadsheet key and etc.
The information necessary to customize is the same as the article
"[GAS]Creation of the data for ManyTime app from Google calendar",
please refer to the article.

The main customization points:

(1)Spreadsheet key of Google Spreadsheet
(2)Calendar ID of Google Calendar
(3)Waiting time - It is set to 10 seconds.
(4)Regional specification - Asia / Tokyo
(5)Newline character - settings \n, for Mac use.

Attentions:
1:As the day goes on, sheets in the spreadsheet will increase.
Delete unnecessary sheets.
2:ManyTime app data will be appended to the Temp-manytime-4.txt file.


(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 toDay.gs and save the script.
5:Select toDay from "Select function" pull-down menu.
6:Press the Run button. Accept the warning dialog at run time depending on
your circumstances.
7:Make sure the contents of Temp-manytime-4.txt on Google Drive.

8:Select Resources>"Current project's triggers" in Script editor.
9:Save the setting of the trigger to start the script toDay.
10:Before the script is run, enter events of the day before to
Google Calendar.


(C)Time trigger settings
1:Select Resources>"Current project's triggers" in Script editor.
2:Press the "Add a new trigger" link in the invoked dialog box.
3:Select "toDay" as "Run" in the dialog box.
4:Select "Time-driven" as "Events" in the dialog box.
5:Select "Day timer" in the dialog box.
6:Set the execution time in the dialog box.
For example, you set "5am to 6am".
"toDay" function will run somewhere in between 5:00 am and 6:00 am.
7:Press Save button in the dialog box.

The "the day before" phrase have used many times in this article,
but you can change it in "the day" or "the next day" by editing the script.


toDay.gs:

//Extract Google Calendar events of the day before, 
//you can create ManyTime data into Google Spreadsheet, and 
//export it to Temp-manytime-4.txt file.
function toDay() {
  var start = new Date(); //The start date is today.
  start.setDate(start.getDate() - 1); //Change start date to the day before.
  //(4)Regional specification - Asia / Tokyo
  var sheetName = Utilities.formatDate(start,"Asia/Tokyo", "yyyyMMdd");
  writeNewSheet(start, sheetName);
  //(3)Waiting time - It is set to 10 seconds.
  Utilities.sleep(10*1000);
  //var csvFileName = 'manytime-4.txt';
  var csvFileNameInRoot = 'Temp-manytime-4.txt'; //Output file name of ManyTime data.
  writeManyTimeFile(csvFileNameInRoot, sheetName);
  //var folderName = "zapier";
  //copyFileToFolder(folderName, csvFileName, csvFileNameInRoot);
}

//Write events of aStart(argument) to the sheet named aSheetName( argument ).
//Hardcode Calendar ID and Google Spreadsheet key.
function writeNewSheet(aStart, aSheetName){

  var ss = SpreadsheetApp.openById( 'Spreadsheet key' ), //(1)Spreadsheet key of Google Spreadsheet
      sheet = ss.insertSheet(aSheetName),
      cals = ['Calendar ID'], c, cal, calName, //(2)Calendar ID of Google Calendar
      events, i, details,
      eventslog = [], e,
      rows = [], range,
      anArray, startTime, eventTime,
      hourOffset, eventNumberInDay, aStartDay, previousStartDay,
      anEndDay;

  //Each becomes the target if there is more than one calendar. 
  //The calendar only one here.
  for (c = 0; c < cals.length; c += 1) {

    cal = CalendarApp.getCalendarById(cals[c]);
    calName = cal.getTitle();
    //Get the aStart Event.
    events = cal.getEventsForDay(aStart);
    Logger.log(events.length);
    events = events.reverse(); //Reverse order.
    
    //Eliminate the all-day events that are not in the same day as the aStart
    //from events.
    anArray = new Array();
    //(4)Regional specification - Asia / Tokyo
    startTime = Utilities.formatDate(aStart,"Asia/Tokyo", "yyyyMMdd");
    Logger.log(startTime);
    for (i = 0; i < events.length; i++) {
      if (events[i].isAllDayEvent()) {
        //Get the start date of the event.(4)Regional specification - Asia / Tokyo
        eventTime = Utilities.formatDate(events[i].getStartTime(),"Asia/Tokyo", "yyyyMMdd");
        //Logger.log(eventTime);
        //All-day events
        if (eventTime != startTime) {
          //The start date of all-day event is not the argument aStart.
          continue;
        }
      }
      //It is all-day events of aStart, or usual events of aStart.
      anArray.push(events[i]);
    }
    events = anArray;
    //Logger.log(events.length);
    //From events, eliminate all-day events of the argument anEnd later.
    hourOffset = 0; //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 (4)Regional specification - Asia / Tokyo
          aStartDay = Utilities.formatDate(event.getStartTime(),"Asia/Tokyo", "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);
          //(4)Regional specification - Asia / Tokyo
          anEndDay = Utilities.formatDate(anotherEndTime,"Asia/Tokyo", "yyyyMMdd");
          var endDateString = "";
          if (aStartDay != anEndDay) {
            //Write "end date" in a comment.(4)Regional specification - Asia / Tokyo
            endDateString = Utilities.formatDate(anotherEndTime,"Asia/Tokyo", "yyyy-MM-dd");
            endDateString = "(~" + endDateString + ")";
          }
          //
          return {
            time: new Date(event.getStartTime()).getTime(), // sort by this
            details: [
            //(4)Regional specification - Asia / Tokyo
              Utilities.formatDate(newStartTime,"Asia/Tokyo", "yyyy-MM-dd HH:mm:ss@#"),
              Utilities.formatDate(newEndTime,"Asia/Tokyo", "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: [
              //(4)Regional specification - Asia / Tokyo
              Utilities.formatDate(event.getStartTime(),"Asia/Tokyo", "yyyy-MM-dd HH:mm:ss@#"),
              Utilities.formatDate(newEndTime,"Asia/Tokyo", "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 the number of rows is 0, it will be an error.
  if (rows.length > 0) {
    //range = sheet.getRange(1, 1, rows.length, 3);
    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 writeManyTimeFile(aCsvFileName, aSheetName) {

  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(aSheetName);
  var maxColumn = ss.getLastColumn() - 1;
  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 the content for exporting that extracts 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 @". 
        //Logger.log(data[row][col].toString().indexOf("@#") != -1);
        if (data[row][col].toString().indexOf("@#") != -1) {
          data[row][col] = data[row][col].replace(/@#$/, "");
        }
      }
      //(5)Newline character - settings \n, for Mac use.
      csv += data[row].join("\t") + "\n";
    }
    csvFile = csv;
  }
  //Get the file of argument aCsvFileName
  /* 2015-04-25
  var files = DocsList.getAllFiles();
  var file;
  for (var i in files) {
    if (files[i].getName() == aCsvFileName) {
      file = files[i];
      break; 
    }
  }
  */
  var files = DriveApp.getFiles();
  var file = null;
  while (files.hasNext()) {
    file = files.next();
    if (file.getName() == aCsvFileName) {
      break;
    }
    file = null;
  }
  //The variable file if it exists, add lines of ManyTime data to 
  //the end of the existing file.
  //If it does not exist, create a new file with the name of the aCsvFileName argument.
  if (file) {
    //file.append(csvFile); 2015-04-25
    var theContent = file.getAs('text/plain').getDataAsString();
    var resString = theContent.concat(csvFile);
    file.setContent(resString);
  } else {
    /* 2015-04-25
    var dataBlob;
    dataBlob = Utilities.newBlob(csvFile, 'text/plain', aCsvFileName);
    DocsList.createFile(dataBlob);
    */
    DriveApp.createFile(aCsvFileName, csvFile, 'text/plain');
  }
}