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:
- Login to your Google account
- Create a new spreadsheet CLICK_ HERE .
3. As in the image below, go to Tools → Script Editor.
Google AppScript Settings:
- 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
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
Authorization is required. Just check the permissions. Then select your Google account.
You will see a screen, just expand ‘Advanced’ and click on ‘Go to YOUR_PROJECT_NAME (insecure)’.
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.
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..