Example Google Apps Script WebApp to read/append to a Spreadsheet

code.js

/*
How to setup
============

1. Go to https://script.google.com and login
2. Click New Project
3. Give the project a name be clicking on "Untitled Project" and giving a new name
4. Copy this whole code and overwrite everything on Code.gs file. Click on the Save icon to save.
5. Go to Google drive and open the worksheet that you want to manipulate
6. Take a look at the url. It should be in this format -- https://docs.google.com/spreadsheets/d/spreadheet-id-as-some-long-seemingly-random-characters/edit#gid=478439860
7. Copy the spreadsheet id from the url, the characters between `/spreadsheets/d/` and `/edit/`
8. Paste the spreadsheet id on the `const SPREADSHEET_ID = 'paste-id-here'
9. Click on the dropdown above (beside the Debug button) and choose authenticate, then click Run
10. A popup window will appear saying `Authorization Required`. Click `Review Permissions`
11. Choose an account you wish to use for this project
12. You're likely to get a prompt saying `Google hasn’t verified this app`. Click `Advanced` then click `Go to your-project-name (unsafe)`
13. Another prompt saying `your-project-name to access your Google Account`. Click Allow
14. This script should now be able to read and write to your spreadsheet
15. To give external tools (Shortcuts, Scriptable, curl, etc) access click Deploy > New Deployment
16. Click `Select Type > Web App`
17. Fill in the fields
      Description: 
      Execute As: choose you account
      Who has access: Anyone  // you need to choos anyone here, otherwise it won't work 
18. Click Deploy
19. Copy the Web App url. This will be the url that will act as the API endpoint for the external tools. 

How to use
==========

Examples below are using `curl`. This shouldn't be difficult to port over to Shortcuts using `Get Contents of URL` or to Scriptable using the Request class.
Each call will return JSON with a "success" key indicating if the operation was successfull or not.

## read the values from A1:B3 on the first wheet
```
curl -L `https://script.google.com/.../exec?range=A1:B2`
# ouput: {"success":true, "values": [["Col1","Col2"],[1,2]] }
```

## read the values from A1:B3 from Sheet2
```
curl -L `https://script.google.com/.../exec?range=Sheet2!A1:B2`
# ouput: {"success":true, "values": [["Col1","Col2"],[1,2]] }
```

## append a row on 3 columns on Sheet2
```
curl -X POST -d '{"data":[1,4,7]}' 'https://script.google.com/.../exec?sheet=Sheet2'
# ouput: {"success":true}
```

Example Shortcut: https://lynks.cc/gsdemoshortcut
*/

const SPREADSHEET_ID = 'paste-id-here'

function authenticate() {
  SpreadsheetApp.openById(SPREADSHEET_ID)
}

// doGet and doPost are special functions by Google Apps Script. 
// This are called automatically when the script is deployed as a Web App.

function doGet(request) {
  try {
    const ws = SpreadsheetApp.openById(SPREADSHEET_ID)
    const range = request.parameter.range
    const values = ws.getRange(range).getValues()
    var retVal = {
      success: true,
      values: values
    }
  } catch(e) {
    var retVal = {
      succes: false,
      error: e.message
    }
  }
  // return in JSON format
  return ContentService.createTextOutput(JSON.stringify(retVal)).setMimeType(ContentService.MimeType.JSON)

}

function doPost(request) {
  try {
    const ws = SpreadsheetApp.openById(SPREADSHEET_ID)
    const sheetName = request.parameter.sheet

    /*
    get the posted data. can be anything but assume that it's in a json format
    this expects the body to be in the format

    { "data": [4,5,6] }

    where 4, 5, and 6 are column values 
    */  
    const body = JSON.parse(request.postData.contents)

    const sheet = ws.getSheetByName(sheetName)
    sheet.appendRow(body.data)
    var retVal = {
      succes: true
    }
  } catch (e) {
    var retVal = {
      succes: false,
      error: e.message
    }
  }
  return ContentService.createTextOutput(JSON.stringify(retVal)).setMimeType(ContentService.MimeType.JSON)

}

// @supermamon