GoogleAppsScriptメモ

Google Apps ScriptのTipsが溜まってきましたので共有します。
気が向いたら更新します。

GoogleDrive

親フォルダの移動

//ターゲットファイルのID
var targetFileId = "0B1ECfqTCcLE8SS1PRnJ1cVgxVlk";
//新しいフォルダのID
var dstFolderId = "0B1ECfqTCcLE8c09YZUtqTkVwU3c";

//ファイルを取得
var targetFile = DriveApp.getFileById(targetFileId);

// 現在の親フォルダを一掃
var parentFolders = targetFile.getParents();
while(parentFolders.hasNext()) {
  var parent = parentFolders.next();
  parent.removeFile(targetFile);
}

// 新しいフォルダに追加する
var dstFolder = DriveApp.getFolderById(dstFolderId);
dstFolder.addFile(targetFile);

テキストファイルを作成・更新

テキストファイルをあるフォルダに作成する。ある場合はコンテンツを追加。

function myFunction() {
  var filename = "testFile.txt";
  var foldername = "testFolder";

  var content = "ほげほげ";

  var dir = DriveApp.getFoldersByName(foldername).next();
  var children = dir.getFilesByName(filename);
  var file = null;
  if (children.hasNext()) {
    // ファイルが有れば内容を追加してテキストを追加
    file = children.next();
    var blob = file.getBlob();
    var text = blob.getDataAsString();
    text += "\n";                       // <-- 改行を追加
    text += (new Date()).toString();    // <-- 日時を追加
    file.setContent(text);
  } else {
    // ファイルがなければ新規作成
    file = dir.createFile(filename, content);
  }
}

特定のフォルダにスプレッドシートがなければ追加・あれば取得

var files = folder.getFilesByName(filename);
var file, ssFile;
if (files.hasNext()) {
  // ファイルが有る場合は、ファイルを取得
  file = files.next();
  // ファイルからスプレッドシートとして開く
  ssFile = SpreadsheetApp.open(file);
} else {
  // ない場合はファイルを作る
  ssFile = SpreadsheetApp.create(newFileName);
  file = DriveApp.getFileById(ssFile.getId());
  folder.addFile(file);
  DriveApp.getRootFolder().removeFile(file);
}

特定のフォルダをzipファイルにする

フォルダサイズが20MBを超えると失敗します。
同一ファイル名がある場合も失敗します。

function createZipFile(request) {

  var targetFolderId = "0B_SnOzJ...0UwOUE";
  var targetFolder = DriveApp.getFolderById(targetFolderId);

  var targetName = targetFolder.getName();
  var cacheFileName = targetName + ".zip";

  // 各ファイルのblobをかき集めて、zipで固める
  var zipped = Utilities.zip(getBlobs(targetFolder, ''), cacheFileName);
  var zipfile = DriveApp.getRootFolder().createFile(zipped);
  zipfile = zipfile.getId();

}
function getBlobs(rootFolder, path) {
  var blobs = [];
  var files = rootFolder.getFiles();

  while (files.hasNext()) {
    var file = files.next();
    blobs.push(file.getBlob());
  }

  var folders = rootFolder.getFolders();
  while (folders.hasNext()) {
    var folder = folders.next();
    var nextFolderPath = path + folder.getName()+'/';
    blobs = blobs.concat(getBlobs(folder, nextFolderPath));
  }
  return blobs;
}

スプレッドシート

カラム番号からA1書式に変える

function getColumnLabel(x) {
  var label = "";

  var alphaLabel = x.toString(26);
  var char;
  for (var i = 0; i < alphaLabel.length; i++) {
    char = parseInt(alphaLabel.substr(i, 1), 26);
    label += String.fromCharCode(64 + char);
  }

  return label;
}

チャートの挿入 or 更新

var targetChartTitle = "my chart";

var chart = null;
var insertChart = true;
var title, embededChartBuilder;

for (var j = 0; j < charts.length; j++) {
  title = charts[j].getOptions().get("title") + "";
  if (title.indexOf(targetChartTitle) > -1) {
    chart = charts[j];
    insertChart = false;
    break;
  }
}

if (insertChart) {
  // チャートの挿入
  embededChartBuilder = mySheet.newChart().asColumnChart().setStacked();
} else {
  // 既存のチャートの更新
  embededChartBuilder = chart.modify().asColumnChart().setStacked();

  // レンジを一回全部削除
  var ranges = chart.getRanges();
  ranges.forEach(function(range) {
    embededChartBuilder.removeRange(range);
  });
}

// チャートの調整
var range = mySheet.getRange(...);
embededChartBuilder.addRange(range);
embededChartBuilder.setPosition(1, 1, 0, 0);
embededChartBuilder.setTitle(targetChartTitle);

//チャートを作成して挿入/更新
chart = embededChartBuilder.build();
if (insertChart) {
  mySheet.insertChart(chart);
} else {
  mySheet.updateChart(chart);
}

その他

Twilioを使ってSMSを送信　

function sendSms(sendToSMS, sendToEmail, message) {
  var accountSid = "";
  var authToken = "";
  var url = "https://api.twilio.com/2010-04-01/Accounts/" + accountSid + "/SMS/Messages.json";

  var smsMessage = message;
  if (smsMessage.length > 160) {
    smsMessage = smsMessage.substring(0, 155) + "...";
  }
  var options = {
    method: "post",
    headers: {
      Authorization: "Basic " + Utilities.base64Encode(accountSid + ":" + authToken)
    },
    payload: {
      From: "",  // Twilioの契約している電話番号
      To: sendToSMS,
      Body: smsMessage
    },
    muteHttpExceptions: true 
  };
  var response = UrlFetchApp.fetch(url, options);
  Logger.log(response);
}

sprintf

/**
sprintf() for JavaScript 0.7-beta1
http://www.diveintojavascript.com/projects/javascript-sprintf

Copyright (c) Alexandru Marasteanu <alexaholic [at) gmail (dot] com>
All rights reserved.

Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions are met:
    * Redistributions of source code must retain the above copyright
      notice, this list of conditions and the following disclaimer.
    * Redistributions in binary form must reproduce the above copyright
      notice, this list of conditions and the following disclaimer in the
      documentation and/or other materials provided with the distribution.
    * Neither the name of sprintf() for JavaScript nor the
      names of its contributors may be used to endorse or promote products
      derived from this software without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
DISCLAIMED. IN NO EVENT SHALL Alexandru Marasteanu BE LIABLE FOR ANY
DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
(INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
(INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.


Changelog:
2010.09.06 - 0.7-beta1
  - features: vsprintf, support for named placeholders
  - enhancements: format cache, reduced global namespace pollution

2010.05.22 - 0.6:
 - reverted to 0.4 and fixed the bug regarding the sign of the number 0
 Note:
 Thanks to Raphael Pigulla <raph (at] n3rd [dot) org> (http://www.n3rd.org/)
 who warned me about a bug in 0.5, I discovered that the last update was
 a regress. I appologize for that.

2010.05.09 - 0.5:
 - bug fix: 0 is now preceeded with a + sign
 - bug fix: the sign was not at the right position on padded results (Kamal Abdali)
 - switched from GPL to BSD license

2007.10.21 - 0.4:
 - unit test and patch (David Baird)

2007.09.17 - 0.3:
 - bug fix: no longer throws exception on empty paramenters (Hans Pufal)

2007.09.11 - 0.2:
 - feature: added argument swapping

2007.04.03 - 0.1:
 - initial release
**/


/*
 * sprintf() for JavaScript is a complete open source JavaScript sprintf implementation.
 * 
 * @param {String} format string
 * @param {Object...values} values
 * @return {String} formatted string.
 */
var sprintf = (function() {
    function get_type(variable) {
        return Object.prototype.toString.call(variable).slice(8, -1).toLowerCase();
    }
    function str_repeat(input, multiplier) {
        for (var output = []; multiplier > 0; output[--multiplier] = input) {/* do nothing */}
        return output.join('');
    }

    var str_format = function() {
        if (!str_format.cache.hasOwnProperty(arguments[0])) {
            str_format.cache[arguments[0]] = str_format.parse(arguments[0]);
        }
        return str_format.format.call(null, str_format.cache[arguments[0]], arguments);
    };

    str_format.format = function(parse_tree, argv) {
        var cursor = 1, tree_length = parse_tree.length, node_type = '', arg, output = [], i, k, match, pad, pad_character, pad_length;
        for (i = 0; i < tree_length; i++) {
            node_type = get_type(parse_tree[i]);
            if (node_type === 'string') {
                output.push(parse_tree[i]);
            }
            else if (node_type === 'array') {
                match = parse_tree[i]; // convenience purposes only
                if (match[2]) { // keyword argument
                    arg = argv[cursor];
                    for (k = 0; k < match[2].length; k++) {
                        if (!arg.hasOwnProperty(match[2][k])) {
                            throw(sprintf('[sprintf] property "%s" does not exist', match[2][k]));
                        }
                        arg = arg[match[2][k]];
                    }
                }
                else if (match[1]) { // positional argument (explicit)
                    arg = argv[match[1]];
                }
                else { // positional argument (implicit)
                    arg = argv[cursor++];
                }

                if (/[^s]/.test(match[8]) && (get_type(arg) != 'number')) {
                    throw(sprintf('[sprintf] expecting number but found %s', get_type(arg)));
                }
                switch (match[8]) {
                    case 'b': arg = arg.toString(2); break;
                    case 'c': arg = String.fromCharCode(arg); break;
                    case 'd': arg = parseInt(arg, 10); break;
                    case 'e': arg = match[7] ? arg.toExponential(match[7]) : arg.toExponential(); break;
                    case 'f': arg = match[7] ? parseFloat(arg).toFixed(match[7]) : parseFloat(arg); break;
                    case 'o': arg = arg.toString(8); break;
                    case 's': arg = ((arg = String(arg)) && match[7] ? arg.substring(0, match[7]) : arg); break;
                    case 'u': arg = Math.abs(arg); break;
                    case 'x': arg = arg.toString(16); break;
                    case 'X': arg = arg.toString(16).toUpperCase(); break;
                }
                arg = (/[def]/.test(match[8]) && match[3] && arg >= 0 ? '+'+ arg : arg);
                pad_character = match[4] ? match[4] == '0' ? '0' : match[4].charAt(1) : ' ';
                pad_length = match[6] - String(arg).length;
                pad = match[6] ? str_repeat(pad_character, pad_length) : '';
                output.push(match[5] ? arg + pad : pad + arg);
            }
        }
        return output.join('');
    };

    str_format.cache = {};

    str_format.parse = function(fmt) {
        var _fmt = fmt, match = [], parse_tree = [], arg_names = 0;
        while (_fmt) {
            if ((match = /^[^\x25]+/.exec(_fmt)) !== null) {
                parse_tree.push(match[0]);
            }
            else if ((match = /^\x25{2}/.exec(_fmt)) !== null) {
                parse_tree.push('%');
            }
            else if ((match = /^\x25(?:([1-9]\d*)\$|\(([^\)]+)\))?(\+)?(0|'[^$])?(-)?(\d+)?(?:\.(\d+))?([b-fosuxX])/.exec(_fmt)) !== null) {
                if (match[2]) {
                    arg_names |= 1;
                    var field_list = [], replacement_field = match[2], field_match = [];
                    if ((field_match = /^([a-z_][a-z_\d]*)/i.exec(replacement_field)) !== null) {
                        field_list.push(field_match[1]);
                        while ((replacement_field = replacement_field.substring(field_match[0].length)) !== '') {
                            if ((field_match = /^\.([a-z_][a-z_\d]*)/i.exec(replacement_field)) !== null) {
                                field_list.push(field_match[1]);
                            }
                            else if ((field_match = /^\[(\d+)\]/.exec(replacement_field)) !== null) {
                                field_list.push(field_match[1]);
                            }
                            else {
                                throw('[sprintf] huh?');
                            }
                        }
                    }
                    else {
                        throw('[sprintf] huh?');
                    }
                    match[2] = field_list;
                }
                else {
                    arg_names |= 2;
                }
                if (arg_names === 3) {
                    throw('[sprintf] mixing positional and named placeholders is not (yet) supported');
                }
                parse_tree.push(match);
            }
            else {
                throw('[sprintf] huh?');
            }
            _fmt = _fmt.substring(match[0].length);
        }
        return parse_tree;
    };

    return str_format;
})();

var vsprintf = function(fmt, argv) {
    argv.unshift(fmt);
    return sprintf.apply(null, argv);
};