NativeScript Tutorial to Integrate Google Sheets

Image for post
Image for post

I was recently asked to create a Native App (compatible with both Android and iOS) that could insert data into a Google Sheet. I’m going to use NativeScript (with Vanilla JavaScript) for this.

The app will consist of two pages:

  1. Screen to receive user input
  2. Screen to confirm user input was received

Set up a Google Sheet for External User Input

I found the article How to Submit an HTML Form to Google Sheets…without Google Forms that explains how to do this. Ultimately instead of using HTML (from a website) we’ll be inserting data from an API; but the concept is the same.

Specify the column headings of the Google Sheet:

Image for post
Image for post

Then follow the instructions from the tutorial above (code also provided below and in the GitHub repository).

Summary from the article:

  • Tools >> Script Editor >> Paste in the code below
  • Save the script (Rename from “untitled project”) but also remember to Save
  • Run >> Run Function >> “setup”
  • Review Permissions / Allow Access
  • Publish >> Deploy as web app
  • In this new menu, specify Execute the app as: Me and Who has access to the app: Anyone, even anonymous
  • Click “Deploy” and then copy the URL that looks like https://script.google.com/macros/s/abcdefghi12345/exec
  • You can close the tab/window for the code

Google Sheets Code

Notes:

  • The step for Run Function was slightly different compared to when the original tutorial was written
  • In the step for Reviewing Permissions, I had to re-sign in to my Google Account. I received a notification about “This app isn’t verified”. I clicked the link for “Advanced” and then “Go To [app name] (unsafe)” anyways. Finally, click the “Allow” button. I did receive a security alert notification email from Google.

NativeScript Code

We’ll have two screens: the “main” screen where the user specifies input, and then a confirmation “thank you” screen. We’ll be using Vanilla JavaScript in NativeScript.

Set up your folder structure like this:

Image for post
Image for post

First, the easy stuff

<Frame defaultPage="pages/home/home"></Frame>

app.css has some basic styling; you can obviously change this

app.js is required to tell the app what to load when the app starts

let application = require("tns-core-modules/application");application.run({ moduleName: "app-root" });/*
Do not place any code after the application has been started as it will not be executed on iOS.
*/

Now for the custom code

Within the “pages” folder, create a “home” folder, and put these (2) files in there:

home.xml is a simple page to display the labels, textboxes, and a submit button

home.js is what processes everything. First, the code:

Explanation of the code

  • Lines 3–7: specify an object that will use two-way binding for the data elements we will pass into Google Sheets
  • Lines 9–11: create the pageLoaded() function to allow for the two-way binding between the XML and the JavaScript
  • Lines 14–32: onSubmit() function for when the user clicks the button
  • Lines 15–17: define the URL by concatenating hard-coded values with the values from the inputs (that were two-way bound). You’ll need to put in your own value for the Google Sheet URL.
  • Lines 19–21: actually call the URL with fetch() . Note, we’re using the function getCommonHeaders() that is defined on lines 34–38 to specify we are passing JSON data.
  • Line 30: navigate to the next page upon completion
  • Lines 40–41: “exports” needed for functions so it’s available in the XML

Finally, create the “Thank You” screen in /pages/thank-you/thank-you.xml

Test it out; it works!

Image for post
Image for post
Confirmation screen of the app
Image for post
Image for post
A new row of data inserted into the Google Sheet

Well, I had to restart my NativeScript playground instance (just re-scanning the QR Code) to get it to work. I think there was some sort of caching issue.

Some Final Notes:

  • To use NativeScript Playground: download the two apps of NativeScript Playground and NativeScript Preview onto your device. Scan the QR code from the Playground website. As you make changes on the website, it auto-updates on your device!
  • You may want to URL-encode the values
  • Consider how to handle blank values (i.e. will you not allowing the user to proceed or will you put in a blank value in the home.js)

Get the Entire Code

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store