Web api with Google Sheet + AppScript

Web api with Google Sheet + AppScript

_ogg_image.PNG

Web api with Google Sheet + AppScript

Google Sheets — AppScript

With Google spreadsheets we can perform operations such as Insert / Update / Delete / Read using Google AppScript. And it can be used as our backend.

Google spreadsheet settings:

  1. Login to your Google account
  2. Create a new spreadsheet CLICK_ HERE .

3. As in the image below, go to Tools → Script Editor.

Image for post

Google AppScript Settings:

image.png

  1. Then you will see the following screen.

Here in this editor , we have to write the code that act as a web API and will intereact GoogleSheets.

2. Below is the AppScript that you should write to the above file and save it.

const sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1')

function doGet(e){
  const action = e.parameter.action;

  //URL_SCRIPT/exec?action=getItems
  if(action == 'getItems'){
    return getItems(e);
  }
}

function getItems(e){
  let records={};
  const rows = sheet.getRange(2, 1, sheet.getLastRow() - 1,sheet.getLastColumn()).getValues();
  const sizeRows = rows.length; 
  let data = [];

  for (let i = 0; i < sizeRows; i++) {
    let row = rows[i],
    record  = {};
    record['Title'] = row[0];
    record['Description']=row[1];
    record['Price']=row[2];
    data.push(record);
   }
  records.items = data;
  let result=JSON.stringify(records);
  return ContentService.createTextOutput(result).setMimeType(ContentService.MimeType.JSON);
}

3. Select from tab, Publish → Deploy as web app

Image for post

4. You will see a window like this, just make sure to select ‘Run the application’ as ‘Me’ and ‘Who has access to the application’ as ‘Anyone, even anonymous’. Every time you perform an update you must choose a new one

Image for post

Authorization is required. Just check the permissions. Then select your Google account.

Image for post

You will see a screen, just expand ‘Advanced’ and click on ‘Go to YOUR_PROJECT_NAME (insecure)’.

Image for post

Allow these permissions and voila! Finally, you will get a window like this with the URL of the web application. Copy that web url for reference. We will use this URL to make HTTP GET requests.

Image for post

Copy the url plus the function that we declare: WEB_APP_URL_SCRIPT / exec? Action = getItems

1. Web Api: https://script.google.com/macros/s/AKfycby01lKNm94FHYORH3_c-lHK35r4zOoZiF8_tOD-QhfujBcglDiE

2. Example Published https://dailyspecialsmenu.netlify.app/

3. AppScript Code gist.github.com/rogergcc/071225ef95055476e4..

https://github.com/rogergcc/specials_menu.git