How to create an Apps Script Web API Endpoint for AJAX connections output Sheet as JSON

by

How to create an Apps Script Web API Endpoint for AJAX connections output Sheet as JSON

doPost and doGet as an endpoint for web applications.

You can setup your webapp to serve as an endpoint for web applications. In this example we explore how to make both GET method requests and POST method requests to the endpoint, retrieving and returning data from a spreadsheet into the JavaScript code as data from an AJAX request.

In this lesson I use Visual Studio Code as my editor.

Also I use LiveServer addon for Visual Studio Code to output the webpage as http locally.

  1. Create an HTML file that has an input field, a button and an element that can be used to output HTML into from the JavaScript code.
  2. Create the doGet() function in yoru Apps Script code. Connect to a spreadsheet with some data that you want to use. Get all the values from the spreadsheet and build an object that can be returned from the sheet data and output into the web app as JSON data.
  3. Use the ContentService to output the webapp content as JSON data, which then can be used by the JavaScript application to connect to the endpoint. return ContentService.createTextOutput(JSON.stringify(holder)).setMimeType(ContentService.MimeType.JSON);
  4. Deploy the web app, and get the exec URL form the deployment to use in your JavaScript Code.
  5. Create a JavaScript file that uses the webapp Exec URL with fetch. Select the DOM page elements as object, attach an eventlistener to the button on the page.
  6. When the button is clicked invoke the function that will make the fetch request to the web app endpoint URL, retrieve the JSON data and output it to the page using JavaScript.

How to use a Web App and make a POST request with JavaScript fetch method to get JSON data back to a web application.

In this example we will connect to a web app URL endpoint with AJAX, sending a value from the input field for the row of data that will be retrieved from the endpoint. This row value can be changed within the input field selecting values from different selected rows coming from a spreadsheet.

  1. Create the POST method in Apps Script to output the values of the spreadsheet data. Select from the e parameters the value of the row to return.
  2. If there is a value for row, use this value to get the response back for the spreadsheet matching row data. To debug you can return the entire e object as a stringified value so that you can see the response in the web application. This content can be difficult to debug since the exec needs to be redeployed on all changes and the dev cannot be used to see the response values.
  3. Update the JavaScript code to get the input value from the input field. Add a UL list to the output element that can then be used to add list items into. With JavaScript you can add page elements using the document.createElement() method. To append them to other existing element use the append() or appendChild() methods.
  4. Create a new FormData object in JavaScript const formData = new FormData();
  5. Add to the formData object the values of the input field under a property name row. formData.append(‘row’,myInput.value);
  6. Send the fetch request using the POST method and attach the body contents from the formData object. fetch(url,{ method:’POST’, body: formData })
  7. Add the response value from the AJAX request to the webpage as a new list item document.createElement(‘li’)

Leave a Comment