Stream

Join this community to post or comment

Bruce Mcpherson
moderator

Tips & tricks  - 
 
Did you know you can access Microsoft OneDrive (and any other MS Live SDK services) from Apps Script. It uses Oauth2 just like the Google Drive API. 

I've just added it to the Apps Script cEzyOauth2 services library.

#gde   #googleappsscript   #blogpost  
10
2
Jacob Jan Tuinstra's profile photoBruce Mcpherson's profile photoKarl Wirén's profile photo
2 comments
 
so far I only looked at the authentication. I'll be doing a jsonapi driver for it sometime this week so i'll find out then,
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
It's Eurovision time again, and no doubt I'll do some analysis of the results when it's all over. I came across some data from predictwise - actually hundreds of predicitions about many things including the odds for Eurovision 2015. 

Here's how to get data from predictwise into a Sheet.

#gde   #googleappsscript   #blogpost  
2
1
Bruce Mcpherson's profile photo
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
Today's snippet is a simple spinner to give you Add-on something to do during those trips back to server side.

#gde   #googleappsscript   #blogpost  
4
Bruce Mcpherson's profile photoEdouard Brasier's profile photo
3 comments
 
Yes I have used a gif too. I find GAS to be slow but I guess it is because I compare it to things running locally like Excel and VBA.
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
The Microsoft equivalent of Add-ons has a pretty neat binding feature that calls you back if any data in a particular sheet  has changed. Apps Script doesn't have that, but then you can do pretty much anything with Apps Script anyway, so here's an app pattern that simulates a sheet binding capability for your add-on

#gde   #googleappsscript   #blogpost  
6
1
Bruce Mcpherson's profile photo
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
Here's a very simple way to organize your apps script files into separate namespaces for a much cleaner project.

#gde   #googleappsscript   #blogpost  
3
3
Spencer Easton's profile photoBruce Mcpherson's profile photoWeb Apps R&D's profile photo김진달's profile photo
3 comments
 
Got it! Very concise thanks. I've been using var self_= this since I saw it in your SJCL library.  It makes chaining a breeze. 
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
Here's another cEzyOauth2 library service implemented. This one is for the Asana API access from Apps Script.

#gde   #googleappsscript   #blogpost  
2
Add a comment...

Spencer Easton

Tips & tricks  - 
 
  Just posted a minor issue of a missing Item type 'VIDEO' in the FormApp ItemType Enum. https://code.google.com/p/google-apps-script-issues/issues/detail?id=5072

  I have a work around of casting the returned getType() to a string and doing a == 'VIDEO' test, but is there a way of overloading the Service Enum to add custom elements?
Google Apps Script issues and feature requests
3
Add a comment...

Alexander Ivanov

Tips & tricks  - 
 
Sharing a folder and a calendar for a submitted email
well its an intranet html page, there is a in input text for the user to enter their email address (any domain) and after clicking submit button, google API should be called to grant access (permission of a folder on google drive) to the submitted email. as well as access to google calender of ...
2
1
Alexander Ivanov's profile photo
Add a comment...

Michael ONeal

Tips & tricks  - 
 
Modifying range names programmatically breaks any formulas in which they are used.

=sum(rangeName) becomes. =sum(#ref)

The lack of ability to create open-ended range names results in the need to add rows to existing range names for data overflows. To do so programmatically the existing range must be removed and then added back with the new definition. This process results in the breakage.

The only work around that I have found is to use the indirect function to include the range name instead of using the range name directly.

=SUM(INDIRECT("rName1"),INDIRECT("rName2"))

instead of:

=SUM(rName1,rName2)

This issue has been addressed in the issue tracker at:

https://code.google.com/p/google-apps-script-issues/issues/detail?id=5048
Google Apps Script issues and feature requests
5
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
+Martin Hawksey redirected a Twitter query in my direction today for how to do reddit oauth2 from apps script, so I added a reddit auth service to the cEzyoauth2 library - here it is.

#gde   #googleappsscript   #blogpost  
3
Martin Hawksey's profile photo
 
Thanks Bruce! :)
Add a comment...

Spencer Easton

Tips & tricks  - 
 
Here is a small library to access the 'Appfolder' in Drive.  It is designed to work with the Oauth2 library.  You can include it with:
MYcf2JNVmwIRS7cARfAZB-Mh00DPSBbB3  

or get the source at the GitHub below. There is an example of the library in action on the github page.

https://github.com/Spencer-Easton/Apps-Script-Appfolder-Library
Apps-Script-Appfolder-Library - This is a Google Apps Script library to access the Appfolder in google drive
5
1
Alexander Ivanov's profile photo
Add a comment...

Ryan Roth

Tips & tricks  - 
 
We've created a new Add-on template: Importing Data to Sheets.

This template shows how you can build a Sheets add-on that pulls data from third-party sources (such as an API) into Google Sheets. It provides a framework and basic UI so that you can drop in code specific to your data source and get it running quickly. It also shows how you can set up triggers to automatically import data over time.

If you've ever need to pull data into Sheets, give it a look!

Happy coding!
12
8
Martin Hawksey's profile photoPedro Márquez Gallardo's profile photoAlex Dobrov's profile photoRuth Conde's profile photo
2 comments
 
FYI. CSS should have "1" suffix, right?
Add a comment...

Web Apps R&D

Tips & tricks  - 
 
How to find a Collaborative Use Case for Google Apps Script
mirror existing processes

It is simple but not obvious, because the limitations of Google Apps Script can conceal it. Thanks to +Federico Granata this visual inspired us to write the following "lesson".

Let us list the obstacles first:

1. Concurrency (multiple user interaction x triggers)
2. Limited compute time (per cycle and per day)
3. Max time per cycle > timebased trigger (5 min)
4. IDE autocomplete for library interfaces (not classes)

Hints on how to overcome these obstacles can be found by studying the contributions provided by +Bruce Mcpherson we just add the following perspectives to show why Bruce is on the right track.

What can be done on client side, should be done on client side. Bruce has provided everything by now that is need for extensive utilization of almost unlimited compute time on client side, without exposing your implementation to re-engineering. Extensive pre-processing of e.g. imported data can be solved this way.

Make sure you have an object-oriented design first. Google Apps Script Service Classes make it very easy to manipulate Google Apps containers such as emails, folders, files, forms, spreadsheets and documents, but you can not derive specialized objects unless you encapsule Service Classes first, i.e. implement your own Mail, Spreadsheet, Sheet, File, Folder etc. class first. After that you can use abstraction to inherit e.g. Google Apps persistency and concurrency into your application classes.

Locking is not enough to manage concurrency, consider using sheet and appendRow to implement a monitor, e.g. when equally authorized user accounts shall not perform changes on a shared container at the same time.

If you have to process huge amounts of e.g. imported data, or a vast stream of incomming data such as e-mails, make sure your model allows delegation. If there is not an explicit link between a specific user in a team and a subset of the data, the application will most likely not be suited for Google Apps and Google Apps Script.

Utilizing triggers in a collaborative use case is based on shared access to containers such as spreadsheets and often works better when the spreadsheet is split into n segments, rather than n users operating on a single spreadsheet.

Try to reduce the need for container based script, otherwise deployment will be difficult, rather implement an Add On or apply JS meta-programming,  if you can not expose the solution in public.
 
Robot Master Chef Cooks 2,000 Recipes, Cleans Up, Does the Dishes

Would you buy it?

More at: http://www.industrytap.com/robot-master-chef-cooks-2000-recipes-cleans-dishes/28765
26 comments on original post
8
2
Federico “Edo” Granata's profile photoAndrés Cifuentes's profile photoAlejandro Camacho Laverde's profile photo
 
I'm glad to be an inspiration :-) 
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
I recently posted about sharing code between client and server side Apps Script. If you use HTMLservice or add-ons, you mind be able to use these tips on how to combine this with namespaces to create reusable code

#gde   #tutorial   #googleappsscript  
1
2
Zig Mandel's profile photoBruce Mcpherson's profile photoKathryn van Nieuwkerk's profile photo
4 comments
 
+Zig Mandel I also have a version that the client exposable scripts are managed from the server side - see manifests in ...
http://ramblings.mcpher.com/Home/excelquirks/gassnips/sharingframe
Add a comment...

Web Apps R&D

Tips & tricks  - 
 
Tree Model
basic library

After Bruce has published his contribution to the subject, we like to bring this http://jnuno.com/tree-model-js to your attention. We only translated it into a Google Apps Script library and did some testing and added the intersection function based on another post in stackoverflow. May be you already have additional functions that would add to the subject. Just contact us.

credit goes to : 
https://plus.google.com/u/1/117805293864114369059/posts

library key : 
1Ig-VDufUXidK64I332Goro6gyebixplAsEW7FbxmTKhekr1zHoIdwc4S
4
Martin Hawksey's profile photo
 
nice find
Add a comment...

Yinon Avraham

Tips & tricks  - 
 
Just posted another post on XML-RPC service methods binding to native JavaScript functions. This is an improvement of my previous post.
2
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
Today's snippet is a very small but useful Apps Script/JavaScript general pattern for recursively traversing a tree.

#gde   #googleappsscript   #blogpost  
5
Web Apps R&D's profile photo
 
This might add to the subject:

// http://jnuno.com/tree-model-js/ ausgewählt weil es gut erklärt wird
// https://github.com/joaonuno/flat-to-nested-js

Tree.walkStrategies = {}

Tree.walkStrategies.pre = 
  function depthFirstPreOrder(callback, context) {
    var i, childCount, keepGoing;
    keepGoing = callback.call(context, this);
    for (i = 0, childCount = this.children.length; i < childCount; i++) {
      if (keepGoing === false) {
        return false;
      }
      keepGoing = depthFirstPreOrder.call(this.children[i], callback, context);
    }
    return keepGoing;
  }

Tree.walkStrategies.post = 
  function depthFirstPostOrder(callback, context) {
    var i, childCount, keepGoing;
    for (i = 0, childCount = this.children.length; i < childCount; i++) {
      keepGoing = depthFirstPostOrder.call(this.children[i], callback, context);
      if (keepGoing === false) {
        return false;
      }
    }
    keepGoing = callback.call(context, this);
    return keepGoing;
  }

Tree.walkStrategies.breadth = 
  function breadthFirst(callback, context) {
    var queue = [this];
    (function processQueue() {
      var i, childCount, node;
      if (queue.length === 0) {
        return;
      }
      node = queue.shift();
      for (i = 0, childCount = node.children.length; i < childCount; i++) {
        queue.push(node.children[i]);
      }
      if (callback.call(context, node) !== false) {
        processQueue();
      }
    })();
  }

function Tree(config) {
  config = config || {};
  this.config = config;
  this.config.childrenPropertyName = config.childrenPropertyName || 'children';
  this.config.modelComparatorFn = config.modelComparatorFn;
}

Tree.prototype.parse = 
  function (model) {
    var i, childCount, node;
    
    if (!(model instanceof Object)) {
      throw new TypeError('Model must be of type object.');
    }
    
    node = new Node(this.config, model);
    if (model[this.config.childrenPropertyName] instanceof Array) {
      if (this.config.modelComparatorFn) {
        model[this.config.childrenPropertyName] = mergeSort(
          this.config.modelComparatorFn,
          model[this.config.childrenPropertyName]);
      }
      for (i = 0, childCount = model[this.config.childrenPropertyName].length; i < childCount; i++) {
        addChildToNode(node, this.parse(model[this.config.childrenPropertyName][i]));
      }
    }
    return node;
  }

function Node(config, model) {
  this.config = config;
  this.model = model;
  this.children = [];
}

Node.prototype.isRoot =
  function () {
    return this.parent === undefined;
  }

Node.prototype.getParent =
  function () {
    return this.parent;
  }


Node.prototype.hasChildren =
  function () {
    return this.children.length > 0;
  }

Node.prototype.getChildren =
  function () {
    return this.children;
  }

Node.prototype.addChild =
  function (child) {
    return addChild(this, child);
  }

Node.prototype.addChildAtIndex = 
  function (child, index) {
    if ( hasComparatorFunction(this) ) {
      throw new Error('Cannot add child at index when using a comparator function.');
    }
    
    return addChild(this, child, index);
  }

Node.prototype.getPath = 
  function () {
    var path = [];
    (function addToPath(node) {
      path.unshift(node);
      if (!node.isRoot()) {
        addToPath(node.parent);
      }
    })(this);
    return path;
  }

Node.prototype.walk = 
  function () {
    var args;
    args = parseArgs.apply(this, arguments);
    Tree.walkStrategies[args.options.strategy].call(this, args.fn, args.ctx);
  }

Node.prototype.all = 
  function () {
    var args, all = [];
    args = parseArgs.apply(this, arguments);
    Tree.walkStrategies[args.options.strategy].call(this, function (node) {
      if (args.fn.call(args.ctx, node)) {
        all.push(node);
      }
    }, args.ctx);
    return all;
  }

Node.prototype.first = 
  function () {
    var args, first;
    args = parseArgs.apply(this, arguments);
    Tree.walkStrategies[args.options.strategy].call(this, function (node) {
      if (args.fn.call(args.ctx, node)) {
        first = node;
        return false;
      }
    }, args.ctx);
    return first;
  }

Node.prototype.drop = 
  function () {
    var indexOfChild;
    if (!this.isRoot()) {
      indexOfChild = this.parent.children.indexOf(this);
      this.parent.children.splice(indexOfChild, 1);
      this.parent.model[this.config.childrenPropertyName].splice(indexOfChild, 1);
      this.parent = undefined;
      delete this.parent;
    }
    return this;
  }

/**
* Create a new FlatToNested object.
*
*@constructor
*@param{object} config The configuration object.
*/
function FlatToNested(config) {
  this.config = config = config || {};
  this.config.id = config.id || 'id';
  this.config.parent = config.parent || 'parent';
  this.config.children = config.children || 'children';
}

/**
* Convert a hierarchy from flat to nested representation.
*
*@param{array} flat The array with the hierachy flat representation.
*/
FlatToNested.prototype.convert = 
  function (flat) {
    var i, len, temp, roots, id, parent, nested, pendingChildOf, flatEl;
    i = 0;
    roots = [];
    temp = {};
    pendingChildOf = {};
    
    for (i, len = flat.length; i < len; i++) {
      flatEl = flat[i];
      id = flatEl[this.config.id];
      parent = flatEl[this.config.parent];
      temp[id] = flatEl;
      if (parent === undefined || parent === null) {
        // Current object has no parent, so it's a root element.
        roots.push(flatEl);
      } else {
        if (temp[parent] !== undefined) {
          // Parent is already in temp, adding the current object to its children array.
          initPush(this.config.children, temp[parent], flatEl);
        } else {
          // Parent for this object is not yet in temp, adding it to pendingChildOf.
          initPush(parent, pendingChildOf, flatEl);
        }
        delete flatEl[this.config.parent];
      }
      if (pendingChildOf[id] !== undefined) {
        // Current object has children pending for it. Adding these to the object.
        multiInitPush(this.config.children, flatEl, pendingChildOf[id]);
      }
    }
    
    if (roots.length === 1) {
      nested = roots[0];
    } else if (roots.length > 1) {
      nested = {};
      nested[this.config.children] = roots;
    } else {
      nested = {};
    }
    return nested;
  };

//* GLOBAL *

function initPush(arrayName, obj, toPush) {
  if (obj[arrayName] === undefined) {
    obj[arrayName] = [];
  }
  obj[arrayName].push(toPush);
}

function multiInitPush(arrayName, obj, toPushArray) {
  var len;
  len = toPushArray.length;
  if (obj[arrayName] === undefined) {
    obj[arrayName] = [];
  }
  while (len-- > 0) {
    obj[arrayName].push(toPushArray.shift());
  }
}

function addChildToNode(node, child) {
  child.parent = node;
  node.children.push(child);
  return child;
}

function hasComparatorFunction(node) {
  return typeof node.config.modelComparatorFn === 'function';
}

function addChild(self, child, insertIndex) {
  var index;
  
  if (!(child instanceof Node)) {
    throw new TypeError('Child must be of type Node.');
  }
  
  child.parent = self;
  if (!(self.model[self.config.childrenPropertyName] instanceof Array)) {
    self.model[self.config.childrenPropertyName] = [];
  }
  
  if (hasComparatorFunction(self)) {
    // Find the index to insert the child
    index = findInsertIndex(
      self.config.modelComparatorFn,
      self.model[self.config.childrenPropertyName],
      child.model);
    
    // Add to the model children
    self.model[self.config.childrenPropertyName].splice(index, 0, child.model);
    
    // Add to the node children
    self.children.splice(index, 0, child);
  } else {
    if (insertIndex === undefined) {
      self.model[self.config.childrenPropertyName].push(child.model);
      self.children.push(child);
    } else {
      if (insertIndex < 0 || insertIndex > self.children.length) {
        throw new Error('Invalid index.');
      }
      self.model[self.config.childrenPropertyName].splice(insertIndex, 0, child.model);
      self.children.splice(index, 0, child);
    }
  }
  return child;
}

/**
* Find the index to insert an element in array keeping the sort order.
*
*@param{function} comparatorFn The comparator function which sorted the array.
*@param{array} arr The sorted array.
*@param{object} el The element to insert.
*/
function findInsertIndex(comparatorFn, arr, el) {
  var i, len;
  for (i = 0, len = arr.length; i < len; i++) {
    if (comparatorFn(arr[i], el) > 0) {
      break;
    }
  }
  return i;
}

/**
* Parse the arguments of traversal functions. These functions can take one optional
* first argument which is an options object. If present, this object will be stored
* in args.options. The only mandatory argument is the callback function which can
* appear in the first or second position (if an options object is given). This
* function will be saved to args.fn. The last optional argument is the context on
* which the callback function will be called. It will be available in args.ctx.
*
*@returns Parsed arguments.
*/
function parseArgs() {
  var args = {};
  if (arguments.length === 1) {
    args.fn = arguments[0];
  } else if (arguments.length === 2) {
    if (typeof arguments[0] === 'function') {
      args.fn = arguments[0];
      args.ctx = arguments[1];
    } else {
      args.options = arguments[0];
      args.fn = arguments[1];
    }
  } else {
    args.options = arguments[0];
    args.fn = arguments[1];
    args.ctx = arguments[2];
  }
  args.options = args.options || {};
  if (!args.options.strategy) {
    args.options.strategy = 'pre';
  }
  if (!Tree.walkStrategies[args.options.strategy]) {
    throw new Error('Unknown tree walk strategy. Valid strategies are \'pre\' [default], \'post\' and \'breadth\'.');
  }
  return args;
}

/**
* Sort an array using the merge sort algorithm.
*
*@param{function} comparatorFn The comparator function.
*@param{array} arr The array to sort.
*@returns{array} The sorted array.
*/
function mergeSort(comparatorFn, arr) {
  var len = arr.length, firstHalf, secondHalf;
  if (len >= 2) {
    firstHalf = arr.slice(0, len / 2);
    secondHalf = arr.slice(len / 2, len);
    return merge(comparatorFn, mergeSort(comparatorFn, firstHalf), mergeSort(comparatorFn, secondHalf));
  } else {
    return arr.slice();
  }
}

/**
* The merge part of the merge sort algorithm.
*
*@param{function} comparatorFn The comparator function.
*@param{array} arr1 The first sorted array.
*@param{array} arr2 The second sorted array.
*@returns{array} The merged and sorted array.
*/
function merge(comparatorFn, arr1, arr2) {
  var result = [], left1 = arr1.length, left2 = arr2.length;
  while (left1 > 0 && left2 > 0) {
    if (comparatorFn(arr1[0], arr2[0]) <= 0) {
      result.push(arr1.shift());
      left1--;
    } else {
      result.push(arr2.shift());
      left2--;
    }
  }
  if (left1 > 0) {
    result.push.apply(result, arr1);
  } else {
    result.push.apply(result, arr2);
  }
  return result;
}
Add a comment...

Edouard Brasier

Tips & tricks  - 
 
I have just followed this tutorial about Google Cloud Datastore and OAuth 2 and it is very good.
https://plus.google.com/102308789325980760043/posts/Hk86zWocRGf
Getting started with Google Cloud Datastore for Google Apps Script Getting started with Google Cloud Datastore Whether or not you are a heavy user of… - Martin John Bramwell - Google+
11
2
Edouard Brasier's profile photoยุทธนา แม่นผล's profile photo김진달's profile photo
Add a comment...

Bruce Mcpherson
moderator

Tips & tricks  - 
 
Here's a super-useful library that can allow you use the Chrome Tracing utility to profile Apps Script. Chrome tracing is a brilliant tool. Now you can use it for Apps script logging and profiling.

#gde   #googleappsscript   #blogpost   #chrome  
7
2
Bruce Mcpherson's profile photoMatthew G. Monteleone's profile photo
Add a comment...