Google Apps Script as a Backend Part I: Google Sheets Integration

If you are like me, when you have an idea, you like to jump on it and build a proof-of-concept app for it. That is all nice and well, but what if it is a shared idea and the other participants know nothing about coding?

Have you ever tried explaining curl or postman to a complete beginner? The intricacies of how to properly make a POST request to fill the database with data can confuse any non-tech-savvy person.

Well, it turns out that you do not really have to do it since just about everyone knows how to enter data in Google Sheets.

In this multi-part guide, I will show you how you can use Google Docs with Google Apps Script — a flavor of Javascript used for programmatic control of Google Apps — as your backend, easily integrate it with a few of the Google Apps, and finally, expose it to the web, so your client app can consume it.

In this first part, we will learn some of the fundamental interactions between Google Sheets and Google Apps Script.

Google Sheets + GAS

This is going to be easy. Create a new folder in your Google Drive and give it a descriptive name, perhaps "GAS Tutorial". Within that, create a new Google Sheets file, and name it Hello World, because why not. Open the Hello World spreadsheet.

You are greeted with the usual empty sheet. Here is where it gets interesting. Click on Tools -> Script Editor, and a new window will take you to the GAS editor — not the best piece of editing software ever, but good enough for our current needs. Name this one Hello World as well, to make it obvious it goes together with the spreadsheet.

In the editor, you will see the empty myFunction placeholder. However, before we get to writing our functions let us first get some necessary details out of the way, so delete the placeholder for now.

To work with spreadsheets in GAS, we will make use of the Spreadsheet service.

First, we will open the desired spreadsheet. To do that, we will need the ID of the spreadsheet, however. You can find it in the web browser's location field when the spreadsheet is open. The ID is the part between the **** of the URL, though yours will obviously be different.
E.g.:
docs.google.com/spreadsheets/d/**27AMFzNschc8zhDZJnpxTfZwHm8x0dKNSsVoB67116Cg**/edit#gid=0

We start the file with the following code:

var spreadsheetId = '27AMFzNschc8zhDZJnpxTfZwHm8x0dKNSsVoB67116Cg'
var spreadsheet = SpreadsheetApp.openById(spreadsheetId);

SpreadsheetApp is the most fundamental class we will be using to handle the Google Sheets CRUD, select ranges, select sheets, etc.

Next, we will populate the sheet with some values, so we have something to work with.

Let us create a price list.

Append the following to the code:

var products = [
  ['lettuce', 4],
  ['angus beef', 10],
  ['black bread', 2],
  ['butter', 2.4],
  ['orange juice', 4.5]
]

Each element of the products array is one row. Each element of the internal arrays is one column.

Now that our price list data is prepared, it is time to add it to the sheet.

var activeSheet = spreadsheet
  .getSheetByName('Sheet1')

function resetPriceList (priceListSheet) {
  priceListSheet
  .getRange('a:b')
  .clear()

  priceListSheet
  .getRange('a1:b' + products.length)
  .setValues(products)
}

resetPriceList(activeSheet)

function myFunction () {
  activeSheet
  .appendRow(['white bread', 1.75])
}

All right, so, what is happening here?

Spreadsheet.getSheetByName() will let you select a specific sheet within the spreadsheet by referencing its name and return its Sheet object.

We then create a little function resetPriceList() that will

1. select a range with Sheet.getRange(),
2. clear it completely with Range.clear(),
3. get a range of the exact dimensions of our products nested array and
4. fill it with the products nested array's values.

Finally, we use the menu option run -> run function -> myFunction to run everything and append another row to the file.

Conclusion

This is it for this part. You now know the basic operations to generate spreadsheets with code. If you are having any problems, you can use Logger.log() to print into the logger (ctrl+enter or View -> Logs).

In the next part, we will turn this script into a very basic web app backend by exposing its functionalities to the internet with a rudimentary API. Stay tuned!

Below, you can find the full code in one piece:

var spreadsheetId = '17NMFzNschc8zhDZJnpxTfZwHm8x0dKNSnVoBl7016Cg'
var spreadsheet = SpreadsheetApp.openById(spreadsheetId)

var products = [
  ['lettuce', 4],
  ['angus beef', 10],
  ['black bread', 2],
  ['butter', 2.4],
  ['orange juice', 4.5]
]

var activeSheet = spreadsheet
  .getSheetByName('Sheet1')

function resetPriceList (priceListSheet) {
  priceListSheet
  .getRange('a:b')
  .clear()

  priceListSheet
  .getRange('a1:b' + products.length)
  .setValues(products)
}

resetPriceList(activeSheet)

function myFunction () {
  activeSheet
  .appendRow(['white bread', 1.75])
}