ApInventor: HOWTO: Get Data from Private Google Sheet with A Google WebApp

I have done this before in another HOWTO: Download Google Sheet as CSV

but this one uses an intermediary Google Apps Script Web App to grab the data
This example uses a google sheet set as accessible only to me and the standalone
google apps script is set similarly. The script runs as me, but can be used by anyone
The app calls the script using the web component, then the script returns the specified
data to the app, which can then be used as a master list, and manipulated thereon
The example shows all the data retrieved, and then offers a spinner to choose a name
from the master list, and shows the email associated with the name
I used the obsfuscated text block for the script link.
Benefits:
  • protects your spreadsheet itself
  • allows anyone to access the data needed for the app, while not storing it on the app
  • easy data manipulation using the lists facility in the app
  • provides a pseudo api solution for other platforms and variants (e.g. Thunkable X at the time of writing)
  • impress your friends 😉
SPREADSHEET

note: I grabbed some dummy data from Mockaroo useful for this sort of thing

SCRIPT

I won’t go into detail about how to create a google apps script web app, well enough covered elsewhere


BLOCKS

Note the work done with the spinner. In the Web.gottext block I replace the first item in the list with the “header” text i want for the spinner.

I use Jorg Kowalski’s development of my spin on resetting the spinner

SCREENSHOT

aia attached – should work using the spreadsheet and script I have set up, or create your own

Ai2GoogleSheetWebApp

Goodbye Awesome Tables :(

It was all going so well… Having found Awesome Tables in 2016, the creation of tables , charts and graphs suddenly became simple and straight-forward, and made me look cool in front of my colleagues :)

Then I started checking the view stats, in November last we hit 926 views! Guess what happens when you (routinely) hit 1000 views, regardless of the number of different tables, and any within the same domain? Awesome Tables slaps a £5000 per annum fee on your usage!

My educational not-for-profit organisation was not going to stump up for that, no way, no how. I needed a solution, and alternative. I knew that Awesome used javascript and Google Visualizations / Charts on its back end (not forgetting the html and css), I therefore decided to apply myself to the somewhat steep learning curve of google charts/visualizations. Continue reading

Google Visualization Table with Modal on Selection

Couldn’t find anything on this, other than a post about angular.js, so had a go at displaying a modal popup after making a row selection on a google visualization table. I had already got to grips with creating modals and used the code provided by W3-Modal.css, so I simply had to put the pieces together. I used the spreadsheet query method for generating data, if done using a google apps script, it is possible (see other posts) to keep the spreadsheet private. I also used the W3.css file for formatting and layout.

The html page created to do all of this is well commented and you can see the results using the demo after the code. Continue reading

Google Visualization – Gauges

This is fun, a simple couple of gauges linked together, one showing revolutions per minute (rpm) the other showing speed, based upon 5th gear in a L322 Range Rover! I guess with more time than I have got it would be possible to code for the entire gearbox 😉

gauges

Continue reading

Google Visualization – Basic Slideshow from Google Sheet

Here is the code for a simple automatic slideshow using google visualizations

Example Slideshow

Google Visualization – Dependent Controls / Filters

When building a chart with controls (using a dashboard), you very often want to filter, then filter again. Lets say we have a people directory, with names, locations, and countries. You create a CategoryFilter for each of these. But it would make sense to filter by country, then be left with only those in that country, before filtering by location. This is how you do it.

Set up your filters:

Set up the table

Now we need to do the binding of the controls and the table:

You can see that we bind the country to the location, then the location to the people, then the people to the table. In this way, the filters become dependent on each other. In the simple demo below, you should see how – when a country is selected only the locations for that country show in the Location drop down.

Here is a full demo of this in operation:

Demo Open in Browser

Google Visualization – CategoryFilter with csvFilter

As mentioned in a previous post, I have been working on generating a replica of the csvFilter option as found in a well known online visualizer for google sheets. In this post I demonstrated how to create a unique list of items that could be used in the control, and now can show the working example.
Continue reading

GAS – getLastRow() when sheet has Array Formula

The benefits of using array formulas in your spreadsheets are manifold, but when you come to grabbing the data held on those sheets for scripting you are presented with an issue: using the getDataRange().getValues function provided in GAS will return the entire sheet, in terms of rows, and therefore a whole load of unnecessary rows shown in your google visualization table or arrays. Continue reading

GAS – Create a Unique list from Cell based csv type Values – two ways

In my endeavours to move away from what might be a costly exercise in providing tables and charts for my users at work, I am delving heavily into google visualisations as a replacement. One feature I have yet to replicate is the “csvFilter”, whereby a column of cells in a table contains more than one value, separated by commas:

csvlist Continue reading