[ZAP]Service of making the data for ManyTime app from Google calendar part2


Dropbox and Google Drive are storage services.
In Part 2, using Zapier that is another kind web service,
connect Dropbox with Google Drive.

Summary is as follows.

1: As the article of Part 1, by using the GAS script, you can set the
time-driven trigger event. As a result, data is appended to
Temp-manytime-4.txt file below Google Drive root.

2: Remove manytime-4.txt if the file exists in zapier folder.
Then you copy from Temp-manytime-4.txt file below Google Drive root,
and paste as "manytime-4.txt" in zapier folder of Google Drive.

Using GAS script that is invoked in the time-driven trigger event,
process of above 1 and above 2 is done in succession.

3: The trigger event occurs when a new manytime-4.txt file is created
in zapier folder of Google Drive. Then, Zap service that will copy
the file from Google Drive onto Dropbox is executed.

1: Joining in the Web service, Zapier
Zapier is a paid Web service that allows you to connect Web services
variety. Zapier has a trial period, and you can also select free plan.
This time, I assume that you can use the Web service Zap that will copy
from files of Google Drive onto Dropbox. On assume that Zapier is available,
I proceed with the following story.
Zapier's URL

2: Creating the zapier folder below Google Drive root
Transferring a file to Dropbox, you can create the zapier folder below
 Google Drive.

(B)About GAS script
GAS script is almost same as part1. To perform the process of the above summary 2, the toDay function added the call of copyFileToFolder function. The script usage is the same as part1. Start the toDay function.

If manytime-4.txt exists in zapier folder, delete the file by copyFileToFolder function. Next, copy Temp-manytime-4.txt below Google Drive root, and then paste it as manytime-4.txt in zapier folder.

(C)Creation of Zap
After registering Zapier, you create the Zap service by logging into the Web site. When the trigger event of Google Drive creates the new file (manytime-4.txt) in zapier folder of Google Drive, this zap service will copy from the file in Google Drive, and then paste it in "Dropbox/Apps/manytime" folder.

Setting procedure of the Zap service is as follow:

Step 1: Choose a trigger and action
Select Google Drive, and select "New File in Folder" as Trigger.
(Source of copy.)

Select Dropbox, and select "Copy File from Trigger" as Action.
(Destination of copy.)

Step 2: Select a Google Drive account
Set the account of Google Drive.

Step 3: Select a Dropbox account
Set the account of Dropbox.

Step 4: Filter Google Drive triggers
Select zapier in Folder(option).

Step 5: Match up Google Drive File in Folder to Dropbox File from Trigger
- Select /Apps/manytime in Directory(required).
- Select File in File(required).
Pressing the right button (Insert GoogleDrive-icon fields), the menu
will be appeared under the text field. Select "File" item from the menu.
"File" icon will be appeared in the left side of the text field.
Specifying the file name is not required.
- Select yes in Overwrite(optional).
- Specify manytime-4 in Specify File Name(optional).
- Specify txt in Specify File Extension(optional).

Step 6: Test this Zap
After pressing the "Test Google Drive trigger" button, you will check
the manytime-4.txt file that is in /Apps/manytime folder of Dropbox.
You need to put manytime-4.txt in zapier folder of Google Drive.
The manytime-4.txt file has some test data.

Step 7: Name and turn this Zap on
Set CopyFromDriveToDropbox in Name this Zap.
(Change to the appropriate Zap name.)
Press the "Turn Zap on" button that will start the CopyFromDriveToDropbox
Zap service.

(D)Checking by ManyTime app
Select the course 4 in ManyTime app, press the sync button.
//By extracting the previous day's Google Calendar events and 
//exporting them to Google spreadsheet, you can create a data 
//for ManyTime app, and export it to Temp-manytime-4.txt file.
//Then, copy the file as manytime-4.txt to zapier under folder.
function toDay() {
  var start = new Date(); //本日 //Today
  start.setDate(start.getDate() - 1); //前日の日付を設定する //Set the start variable to the date of the previous day.
  var sheetName = Utilities.formatDate(start,"Asia/Tokyo", "yyyyMMdd");
  writeNewSheet(start, sheetName);
  //Since the time it takes to process sheet is created, wait 10 seconds.
  //The file name in zapier folder
  var csvFileName = 'manytime-4.txt';
  //The file name below Google Drive root
  var csvFileNameInRoot = 'Temp-manytime-4.txt';
  //Output file name of ManyTime data below Google Drive root.
  writeManyTimeFile(csvFileNameInRoot, sheetName);
  var folderName = "zapier";
  copyFileToFolder(folderName, csvFileName, csvFileNameInRoot);

//Write events in the date of the aStart argument to the sheet 
//with the name of the argument aSheetName.
//Hard-coded Calendar ID and Google Spreadsheet key.
function writeNewSheet(aStart, 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,
      hourOffset, eventNumberInDay, aStartDay, previousStartDay,
  //If there is more than one calendar, each calendar becomes the target. 
  //Here calendar is one.
  for (c = 0; c < cals.length; c += 1) {

    cal = CalendarApp.getCalendarById(cals[c]);
    calName = cal.getTitle();
    //Get events in the date of the aStart argument.
    events = cal.getEventsForDay(aStart);
    events = events.reverse(); //Reverse order.

    //From the events variable, delete all-day events that are not 
    //in the date of the argument aStart . 
    anArray = new Array();
    startTime = Utilities.formatDate(aStart,"Asia/Tokyo", "yyyyMMdd");
    for (i = 0; i < events.length; i++) {
      if (events[i].isAllDayEvent()) {
        eventTime = Utilities.formatDate(events[i].getStartTime(),"Asia/Tokyo", "yyyyMMdd");
        //All-day events
        if (eventTime != startTime) {
          //The start date of all-day event is not the date of the argument aStart.
      //Elements of the anArray variable are all-day events in the date of 
      //the argument aStart, or usual events in the date of the argument aStart.
    events = anArray;
    //Add events in the current calendar to the array of all events.
    hourOffset = 0; //0時~23時 //8:Offset hours, 0 o'clock - 23 o'clock 
    eventNumberInDay = hourOffset - 1; //-1 + hourOffset
    eventslog = eventslog.concat( {
        if (event.isAllDayEvent()) {
          //All-day event
          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); 
          anEndDay = Utilities.formatDate(anotherEndTime,"Asia/Tokyo", "yyyyMMdd");
          var endDateString = "";
          if (aStartDay != anEndDay) {
            //Put the ending date in a comment.
            endDateString = Utilities.formatDate(anotherEndTime,"Asia/Tokyo", "yyyy-MM-dd");
            endDateString = "(~" + endDateString + ")";
          return {
            time: new Date(event.getStartTime()).getTime(), // sort by this
            details: [
              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,
        } 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(),"Asia/Tokyo", "yyyy-MM-dd HH:mm:ss@#"),
              Utilities.formatDate(newEndTime,"Asia/Tokyo", "yyyy-MM-dd HH:mm:ss@#"),
  // 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 = { 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);

//Write ManyTime data to the file of the 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)) {
  var data = ss.getRange(1,1,maxRow,maxColumn).getValues();
  var csvFile = "";
  //Create the content to export by extracting 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(/@#$/, "");
      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];
  var files = DriveApp.getFiles();
  var file = null;
  while (files.hasNext()) {
    file =;
    if (file.getName() == aCsvFileName) {
    file = null;
  //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); 2015-04-25
    var theContent = file.getAs('text/plain').getDataAsString();
    var resString = theContent.concat(csvFile);
  } else {
    /* 2015-04-25
    var dataBlob;
    dataBlob = Utilities.newBlob(csvFile, 'text/plain', aCsvFileName);
    DriveApp.createFile(aCsvFileName, csvFile, 'text/plain');
//Copy the file that specified by aFileNameInRoot below Google Drive root.
//And paste the file that renamed to the aFileName variable
//under aFolderName folder. 
function copyFileToFolder(aFolderName, aFileName, aFileNameInRoot) {
  //var targetFolder = DocsList.getFolder(aFolderName); 2015-04-25
  var targetFolders = DriveApp.getFoldersByName(aFolderName);
  var targetFolder =;
  //If there is the previous file under aFolderName folder (aFileName), delete it.
  var removeDoc = getFileByName(targetFolder, aFileName);
  if (removeDoc) {
  //Copy the file that specified by aFileNameInRoot in Google Drive root. 
  //And paste the file that renamed to the aFileName variable under 
  //aFolderName folder.
  /* 2015-04-25
  var mainDoc = getFileByName(DocsList.getRootFolder(), aFileNameInRoot);
  var mainDoc = getFileByName(DriveApp.getRootFolder(), aFileNameInRoot);
  mainDoc.makeCopy(aFileName, targetFolder);

//Return the file that have the file name (specified by the filename argument) 
//under the folder (specified by the folder argument).
function getFileByName(folder, filename) {
  /* 2015-04-25
  var files = folder.find(filename);    
  for( var i in files){
    if ( files[i].getName() == filename ) 
      Logger.log("found " + files[i].getName());
      return files[i];
  } //for i in files
  var file;
  var files = folder.getFilesByName(filename);
  while (files.hasNext()) {
    file =;
    if (file.getName() == filename) {
      Logger.log("found " + file.getName());
      return file;
  Logger.log("did not find " + filename);
} //getFileByName

getFileByName function is from the following article.

[GAS]Service of maki..Use of a spreadsheet.. ブログトップ