NativeScript Tutorial to Integrate Google Sheets
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:
- Screen to receive user input
- Screen to confirm user input was received
Set up a Google Sheet for External User Input
There are articles on when/if you use use Google Sheets to store data (vs a “real” database). For the purpose of this app, and more importantly the request of the customer, we’re going to do it.
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:
Then follow the instructions from the tutorial above (code also provided below and in the GitHub repository).
Summary from the article:
- Create a new Google Sheet and specify your headers
- 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
For this tutorial I’m using the NativeScript Playground, but you can of course write the code on your local computer.
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:
First, the easy stuff
app-root.xml indicates which screen to show when the app loads
<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
I’ve decided to put each screen (XML+ JS) in a subdirectory of the “pages” folder.
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
- Line 1: allow us to use the “fetch” module, which is used to access external websites
- 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!
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:
- The headers in the spreadsheet must match exactly the inputs from the REST API that you’re using (i.e. the variable names in the URL must have the same capitalization)
- 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)
