twotix

/** 
API DOC: https://developers.google.com/google-apps/spreadsheets/
Some things to remember:

- Get the Sheet ID "gid" by using the https://developers.google.com/google-apps/spreadsheets/#retrieving_information_about_worksheets
- Google API cannot handle spaces in it's SQL queries - make sure to wrap queries in " (not ')
- Column A is a "label" column - cannot query on it. Hide it
- Google returns results row contents a comma separated cell - googleParse function can convert into an object - including cell contents with commas. Cells with ': ' (including the space) may break it.

**/

twotix

問題・目的

• 特定のスプレッドシートだけを外から更新したい
• OAuth は大変めんどうかつ、特定のスプレッドシートのみ権限を与えることができない
• Google Spread Sheet の XML API が全力で面倒

方法

1. いじりたいスプレッドシートで [ツール] → [スクリプトエディタ…] を開く (Google ドライブの新規作成から、スクリプトを選んでもいい)
2. スクリプトエディタに code.gs の内容をはっつける。

twotix

function alchemy() {
  var ss = SpreadsheetApp.openById("");
var sheet = ss.getSheetByName("Bookmarks");
   
  var lrow = sheet.getLastRow();
 
  
   var dataRange = sheet.getRange(2, 1, lrow, 6);
 var data = dataRange.getValues();
  

twotix

function alchemy() {
  var ss = SpreadsheetApp.openById("");
var sheet = ss.getSheetByName("Bookmarks");
   
  var lrow = sheet.getLastRow();
 
  
   var dataRange = sheet.getRange(2, 1, lrow, 7);
 var data = dataRange.getValues();
  

twotix

/*   
   Copyright 2011 Martin Hawksey and Dito LLC 

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software

twotix

// Source: http://www.pipetree.com/qmacro/blog/2011/10/automated-email-to-task-mechanism-with-google-apps-script/
    // -----------------------------------------------------  
    // Globals, contants  
    // -----------------------------------------------------  
    TASKLIST = "Ittichai's list";  
    LABEL_PENDING = "newtask";  
    LABEL_DONE = "newtaskdone";  
      
    // -----------------------------------------------------  
    // getTasklistId_(tasklistName)  

twotix

function onOpen() {
  var menuEntries = [ {name: "Create Diary Doc from Sheet", functionName: "createDocFromSheet"}];
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.addMenu("Fitness Diaries", menuEntries);
}

function createDocFromSheet(){
  var templateid = "1O4afl8SZmMxMFpAiN16VZIddJDaFdeRBbFyBtJvepwM"; // get template file id
  var FOLDER_NAME = "Fitness Diaries"; // folder name of where to put completed diaries
  // get the data from an individual user

twotix

function getJSON(aUrl,sheetname) {
  //var sheetname = "test";
  //var aUrl = "http://pipes.yahoo.com/pipes/pipe.run?_id=286bbb1d8d30f65b54173b3b752fa4d9&_render=json";
  var response = UrlFetchApp.fetch(aUrl); // get feed
  var dataAll = Utilities.jsonParse(response.getContentText()); //
  var data = dataAll.value.items;
  for (i in data){
    data[i].pubDate = new Date(data[i].pubDate);
    data[i].start = data[i].pubDate;
  }

twotix

// If not installed from the Apps Script Library to start form processing select Run > onInstall twice (once to authnticate), the second time to add the trigger
function doOnFormSumbit(e) {
  var doc = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheetName = e.values[1]; // assuming field to split data by is first form element
  // see if new sheet
  var sheetToWrite = doc.getSheetByName(sheetName) || null;
  // if new sheet insert column headers
  if (sheetToWrite == null){
    var sheets = doc.getSheets();
    var sheetToWrite = doc.insertSheet(sheetName);

twotix

// This code excluding Google(c) Code tab:
/*   
   Copyright 2011 Martin Hawksey

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0