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

[TimAI2]

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

function doGet() {
var ss = SpreadsheetApp.openById('ENTER YOUR SPREADSHEET ID HERE');

var sh = ss.getSheets()[0]; //if you keep your sheet tabs in the same place, you can select any sheet tab

var rg = sh.getDataRange().getValues(); //fetches all the data values on the sheet tab

var outString = '';

  for(var row=0 ; row<rg.length ; ++row){

    outString += rg[row].join(',') + ';\n';  //this section adds line returns to the rows in your data

  }

  

return ContentService.createTextOutput(outString).setMimeType(ContentService.MimeType.TEXT);  //returns the data to the app

}

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