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

/*   
   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

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

twotix

function youtube(){
  // Setup OAuthServiceConfig
  var oAuthConfig = UrlFetchApp.addOAuthService("youtube");
  oAuthConfig.setAccessTokenUrl("https://www.google.com/accounts/OAuthGetAccessToken");
  oAuthConfig.setRequestTokenUrl("https://www.google.com/accounts/OAuthGetRequestToken?scope=http%3A%2F%2Fgdata.youtube.com%2F");
  oAuthConfig.setAuthorizationUrl("https://www.google.com/accounts/OAuthAuthorizeToken");
  oAuthConfig.setConsumerKey("anonymous");
  oAuthConfig.setConsumerSecret("anonymous");

  // Setup optional parameters to point request at OAuthConfigService.  The "twitter"

twotix

function doGet(e){
  // get some variables passed from the querystring
  var project = e.parameter.title;
  var range = e.parameter.range;
  var sheet = e.parameter.sheet;
  // Grab a basic html template to fill in the blanks - see https://developers.google.com/apps-script/html_service
  var t = HtmlService.createTemplateFromFile("reportTemplate");

  // some bits of code to grab the source urls
  var doc = SpreadsheetApp.openById(ScriptProperties.getProperty('active'));

twotix

function youtube(){
  // Setup OAuthServiceConfig
  var oAuthConfig = UrlFetchApp.addOAuthService("youtube");
  oAuthConfig.setAccessTokenUrl("https://www.google.com/accounts/OAuthGetAccessToken");
  oAuthConfig.setRequestTokenUrl("https://www.google.com/accounts/OAuthGetRequestToken?scope=http%3A%2F%2Fgdata.youtube.com%2F");
  oAuthConfig.setAuthorizationUrl("https://www.google.com/accounts/OAuthAuthorizeToken");
  oAuthConfig.setConsumerKey("anonymous");
  oAuthConfig.setConsumerSecret("anonymous");

  // Setup optional parameters to point request at OAuthConfigService.  The "twitter"