BFinc

Google Apps Script Survey Workflow

The components involved in this workflow are:

• A script to generate and send an email with an HTML form.
• An html template for that email, which allows us to customize the email for each recipient.
• A doPost() function to handle responses. The script must be [deployed as a Web App][1].
• A spreadsheet to collect responses. The script will be contained in the spreadsheet, and extends the spreadsheet UI with a menu for sending a copy of the survey. (It could be adapted for standalone use, without the UI component.)

Here is an example of such a workflow, conducting a Commuting Survey. Recipients will receive a survey email like this:

BFinc

/**
 * Connect and fetch Salesforce data via OAuth
 */
function queryDataFromSalesforce() {
  // Read OAuth consumer key / secret of this client app from script properties, 
  // which can be issued from Salesforce's remote access setting in advance.
  var sfConsumerKey = ScriptProperties.getProperty("sfConsumerKey");
  var sfConsumerSecret = ScriptProperties.getProperty("sfConsumerSecret");
  if (!sfConsumerKey || !sfConsumerSecret) {
    Browser.msgBox("Register Salesforce OAuth Consumer Key and Secret in Script Properties");

BFinc

var AUTHORIZATION_URL = "https://login.salesforce.com/services/oauth2/authorize?response_type=code&client_id={client_id}&redirect_uri={redirect_uri}&state={state}";
var ACCESS_TOKEN_URL = "https://login.salesforce.com/services/oauth2/token";
var CLIENT_ID = "input your client_id";
var CLIENT_SECRET = "input your client_secret";
var REDIRECT_URI = "https://script.google.com/macros/s/********************/usercallback";
 
/**
 * メニューの設定
 */
function onOpen() {

BFinc

/**
 *  ---  Continous Execution Library ---
 *
 *  Copyright (c) 2013 Patrick Martinent
 *
 *  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

BFinc

Script to sync a Google SpreadSheet to a Fusion Table

Save for a few legacy projects that still use Fusion Tables I don't actively use this script anymore. This update hopefully solves the OAuth issue that cropped up once Google depcricated the Client Login method used by the prior version.

As always, your mileage may vary, and I welcome someone -- Google or otherwise -- to offer a long-term maintained solution.

The following is largely cribbed from a Google example here. I try to explain some of the API settings that must be enabled in the Developer's Console and elsewhere

Create your spr

BFinc

/** * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * httpGetTemplate.gs
 *
 * A pair of template functions for external host communication with
 * UrlFetchApp, including parameter encoding and error handling.
 *
 * From: gist.github.com/mogsdad/a76c32231a2b91ff8b59
 *
 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
   

BFinc

/**
 * Converts an XML string to a JSON object, using logic similar to the
 * sunset method Xml.parse().
 * @param {string} xml The XML to parse.
 * @returns {Object} The parsed XML.
 */
function xmlToJson(xml) { 
  var doc = XmlService.parse(xml);
  var result = {};
  var root = doc.getRootElement();

BFinc

Google Apps Script pdfToText Utility#

This is a helper function that will convert a given PDF file blob into text, as well as offering options to save the original PDF, intermediate Google Doc, and/or final plain text files. Additionally, the language used for Optical Character Recognition (OCR) may be specified, defaulting to 'en' (English).

Note: Updated 12 May 2015 due to deprecation of DocsList. Thanks to Bruce McPherson for the getDriveFolderFromPath() utility.

    // Start with a Blob object
    var blob = gmailAttchment.getAs(MimeType.PDF);
    

BFinc

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 = JSON.parse(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;
  }

BFinc

var request = require('request')
var JSONStream = require('JSONStream')
 
var opts = {
  url: 'https://api.github.com/users/watson/repos',
  headers: {'User-Agent': 'request'}
}

request(opts)
  .pipe(JSONStream.parse('*'))