Thursday, September 8, 2016

Find the best location for your next shop in Google Spreadsheet (Voronoi Algorithm)

About 14 months ago I wondered how a Czech retail market is divided into regions according to their locations. Where is the best place to build next shop? I remembered to a Voronoi algoritm from university that could divide desire an area into small parts along the nearest spot.

Let's check a definition from Wikipedia
Voronoi diagram is a partitioning of a plane into regions based on distance to points in a specific subset of the plane. That set of points (called seeds, sites, or generators) is specified beforehand, and for each seed there is a corresponding region consisting of all points closer to that seed than to any other.

Why should you upload your a dataset into an unknown online web application when you want to visualize data? There were a lot of examples on the internet, but I wanted to create as easy as possible for everyone. Finally I found article by Chris Zettter, where code was written over Leaflet and Open Street map layer. It inspired me to rewrite as Google Add-on  to Google Spreadsheet with Google Maps underlay (I love Google Maps SDK)

I will introduce you shortly how to use Voronoi Map from scratch in lesss than 5 minutes.

1) Create a new Google Spreadsheet. Then insert data into Sheet 

For this demo I've downloaded data of California retailers from this Fusion Tables and filtered only Walgreens stores in San Francisco (my favorite place :-). 

You need to prepare data as separate columns for Name, Latitude, Longitude nad Type of point of interest. I've added a new column "brand" (green color) as a Type and filled it with text "Walgreens"

2) Open menu Add-ons and select Get add-ons. Find voronoi map and than click at + FREE button for install.

3) After installation open menu Add-ons -> Voroni Map and select Create Voronoi Map

4) Select appropriate colums and click Create map

Now you see a result. The blue points are point-of-interest (Walgreens stores) and regions along the nearest point. If you have more type of points-of-interest, you can filter them in right top panel.

Probably you are thinking where you should built you next shop? I would recommend you find intersection of region and think about this location :-)

Tuesday, February 23, 2016

Analyse city traffic from webcams through Google Cloud Vision API

The Google has recently introduced Cloud Vision API, which allows you to analyse image data through API instead of creating custom algorithms.

As Google Developer Expert I was involed in tester program of this API during alpha stage. My GDE's friend +RiĆ«l Notermans inspired me to connect Vision API into my favorite Google Apps Script. I was thinking how to use it and suddenly I found out useful something-like smart city solution.


I'm living in Prague. The municipal transportation provides almost real-time records of traffic from webcams The images are available on web ( Anyone could check traffic before heading out to work.  I am able to connect, download and send these images to any API, because each image has own public URL

The maps of webcams in Prague

I have created Google Spreadsheet  as a database with new a Google Apps Script project.
Next step was activated required API in Google Developer Console. OAuth 2.0 dance was managed by great cGoa library by +Bruce Mcpherson. Cloud Vision "client" library was generated from Cloud Endpoints by +Spencer Easton's code.

My script fetched the image from URL as a Blob, converted into base64 and sent to Cloud Vision API with parameter for LABEL_DETECTION (Label detection tutorial available here)

As a JSON response I received description of image as labels with confidence score (min 0 - max 1).
The last step was setup Trigger for automatic run Apps Script every 5 minutes.

The result

I have run my script on webcam at Argentinska street at February 17th between 12am-12pm. The all collected data is available as interactive visualization.

The label "road" means, that higher value looks like only street without street, so traffic during lunch was low.

The chart for label traffic looks like inverse function of road label. The higher value means, that Google's recognizes traffic on the image with greater confidence.

It absolutely not 100% accurate, but it could inspire you how to use Google Cloud Vision API.

Sunday, February 14, 2016

Integrate Google Apps and Slack with Google Apps Scripts (Incoming Webhooks)

Google Apps and Slack are two the most popular productivity tools for unicorn startups (=early stage companies with valuation $1B+).  Almost 80% unicorn startups are using Google Apps as primary platform for email, calendar or storage and 50%  of them are using Slack for internal communication [source]
It means that nearly half of these cool startups could integrate their internal process with Google Apps Scripts.

Slack API has several possible ways how to integrate your application/script into their platform. Today we will start the simplest way - through Incoming Webhooks.  Webhooks are solid and unique published URL address, which allows you send any data, which will be saved into channels.

The small guide how to integrate Google Apps with Slack in 10 minutes

1) Visit link You will be redirected into settings page of your board.

2) Select the target channel (e.g #tech) - don't worry you can change afterwards. Next click at green button Add Incoming WebHooks integration. 

3)  The remain Slack webpage contains settings like name of the robot (=it's not actually pseudo-robot), icon, default channel and so on. We only need to copy webhook url. (e.g.

4) Create a new Google Apps Script project and copy code snippet. Insert your webhook url from previous

5) Now run START() function in Apps Script. If you insert right url, you will see a new message is relevant channel.

Now you have connect your Google Apps Script with Slack. You can simply load data from Calendar, Gmail, Drive or Sites and create alert on events. I will show you in some next article.

I recommend check tips how to format text, add links or attachments:

Are planning to integrate your Google Apps with Slack? Let your ideas in comments section

Sunday, January 24, 2016

Easy data scraping with Google Apps Script in 5 minutes

I'm using Google Apps Script for a lot of things - from automate tasks to data analysis. I have discovered, that there was repetitive use-case: scrape data from web  and parse exact value from HTML source code. If you are novice in programming, you probably know, that's difficult to write and use regular expresion. For me too :) I have written Google Apps Script library, which helps you to parse data in 5 minutes.

Let's create a small example. In our company we have created a Google Apps application SignatureSatori to create and setup email signatures for all users in domain. Like a good growth hacker I benchmark the competitors how quickly get new users. There is a Google Apps Marketplace, which estimate number of users. I need save that numbers each day.

1) Create a new Google Apps Script and insert a new library (Resources -> Library)

Parser library takes three parameters - input text and pattern which bounds desired text.
Parser // name of library
    .data(content) // input text
    .from(fromText) // from text pattern
    .to(toText) // to text pattern
    .build(); // run parser and return value

2) Now open desired web-page (e.g Chrome Webstore in our case). Click on specify HTML element by right mouse button and select Inspect element.

3) Find the right part of HTML and copy fromText and toText

4) Now we have all required information to complete script
function getData() {
    var url = "";
    var fromText = '<span class="e-f-ih" title="';
    var toText = '">';
    var content = UrlFetchApp.fetch(url).getContentText();
    var scraped = Parser
    return scraped;

5) The last and the easiest step is copy parsed data into Spreadsheet
function SAVE_DATA() {
 var sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName); // insert Spreadsheet Id and Sheet name
 sheet.appendRow([ new Date(), getData() ]);

6) If you want to log during scraping (e.g. if you want to debug wrong value), call .setLog() function before final .build() function:
Completed code of Parser library Enjoy!

Sunday, October 11, 2015

Hosting for your files instead of Google Drive #2 (Firebase)

In my previous blogpost I have described first alternative for hosting files instead of Google Drive - Google Storage.

Today I would like to introduce another alternative - Firebase. It is a real-time database for saving JSON objects through REST API. The Firebase also offers hosting for your files, which are served under HTTPS.  Moreover, a free plan is available. You can upload for hosting 5 GB with max. traffic 30 GB. There is also a paid plan, where you can get more storage/transfer and setup custom domain. (Firebase pricing)
Let's go!

1) First open console with your Google Account

2) Create new project (or import your Google Cloud project and connect to it)

3) Choose project name (I recommend to choose more memorable URL address lik Dont forget that must be unique)

4) Now you have to install firebase tools on your computer via npm.
Note. If you have never used npm to install app, please download and install nodejs (npm - node package manager is included)

If you have already installed nodejs, you can type into shell/command line
npm install -g firebase-tools

5) Login into your Google Account. Type
firebase login
and authorize access in opened browser

6) Type first command  to run code initialization.
firebase init

7) Then select Database row with arrows keys UP/DOWN and press SPACE key.
We want to confgure and deploy only Hosting options.
( ) Database
(*) Hosting

8) Select Firebase project

9) Choose which directory will be setup as public (=contains files hosted on Firebase). Public is as a default opt

10) Don't configure as single-page app, because all assets will be redirect to index.html (press N)

11) The last step is to deploy files into Firebase by code (Rememeber, that you should be in your directory)
firebase deploy

12) Now you can access your project at URL name

Every deployment is logged in dashboard, where you can revert into any previous version/state.

Sunday, October 4, 2015

Hosting for your files instead of Google Drive (Google Storage)

A lot of not only Google Apps Script developers and users have used Google Drive feature to host any file as webhosting. It was useful for CSS files, images, JavaScript libraries, which can be used in code. Deployment was very easy - you have just copied files into specific folder and change sharing options.

Google recently announced, that hosting feature will be deprecated (more information here). Users have, according to deprecation policy, one year to find out a new place where to store files.

I was thinking about several options, so I would like to bring some alternatives.

The first one is Google Storage, which is a part of Google Cloud platform

Google Storage allows you to host any file with advanced possibilities like control who can read/write, monitor of traffic etc.

The main advantage is connection with App Script. Every Apps Script project has also Cloud project in Developers console (

From Google Apps Script editor choose Resources --> Advanced Google services

In the new window, click at link "These services must also be enabled in the Google Developers Console."

In Developer console,  select Storage - Cloud storage. If you open console for the first time, you have to click button Enable billing (In this section you will fill in credentials like your name or number of credit card)

Now it is time to Create a bucket. Have you ever heard about it? The bucket is virtual space, where your objects (files) are stored. You can have serveral buckets for one Cloud project

Select name of the bucket. Important - this name must be unique in all Google Cloud Storage platform

Now you can create a new folder inside the bucket and upload files - by clicking button or drag'n'drop like in Google Drives. It is similar, isn't it ?

The last thing is publish on the web by click on checkbox

The final URI will like:
Now you can insert your file in your Apps Script HTMLService code or send link to someone else.

Google Storage is a paid service, but the price is low and according to using of service (=cloud).There are three main questions for you
  1. How much data do you need to store ? (1GB per month = $0.026)
  2. How many requests do you do? (10 000 requests for $0.01)
  3. What is total traffic through your bucket ( 1GB of traffic in EMEA/AMERICA= $0.12)

So if you are planning to host 10 MB ($0.01) with total loading 100 000x ($0.01) and traffic 1GB ($0.12), you will finally get receipt $0.14 for month!

Google prepared online tool to get information about price:

I am just starting use this way to host files, but I have already recognize "best pattern". Its looks like, that is better to create one main project in Developer Console, which wont be connected to any GAS  project. Then for every GAS project create a new bucket.. 

Friday, August 28, 2015

Material Design into your Google Apps Script application

Google recently introduced Material Design Lite, which is library of components and templates in pure CSS, HTML and JavaScript without any Polymer implementation.

This is useful for your Google Apps Script web-apps, because you just insert CSS into your rendered HTMLSevice. You don't have to vulcanize html files

At top of your code insert this snippet

<link rel="stylesheet" href="">
<script src=""></script>
<link rel="stylesheet" href="">

Now you can add class name into your already written HTML elements. For example if you have created

<button class="mdl-button mdl-js-button mdl-button--raised mdl-js-ripple-effect mdl-button--accent">Send form</button>

Modularity first! Google offers customize your color scheme. There is online tool, where you can choose primary and secondary color. The generated unique URL defined your setup.

I have already implemented Material Design in public.

At AppSatori we launched free tool Google Apps Finder. If you typed domain, you get response whether company using Google Apps.

I needed create easy and fast first prototype. Instead of creating design and graphic elements, I have put together all material elements.

Live demo is available at