Apps Script Sheets to API endpoint webapp

by

To create a web application using Google Apps Script that outputs the contents of a Google Spreadsheet into a client-side JavaScript object, you’ll need to write code for both the server-side (Google Apps Script) and the client-side (HTML with embedded JavaScript). Here’s how you can set it up:

1. Google Apps Script: Server-Side

First, you’ll create a Google Apps Script that publishes the content of a Google Spreadsheet as a web app. This script will use the doGet function to serve data to the client-side.

  • Open Google Sheets and select “Extensions” -> “Apps Script”.
  • Delete any code in the script editor and replace it with the following:
function doGet(e) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();
  const jsonData = JSON.stringify(data);
  
  return ContentService.createTextOutput(jsonData)
    .setMimeType(ContentService.MimeType.JSON);
}

function setup() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  sheet.getRange("A1").setValue("Name");
  sheet.getRange("A2").setValue("Alice");
  sheet.getRange("A3").setValue("Bob");
  // Set more initial data as needed
}
  • Run setup function from the script editor to populate some initial data in your spreadsheet.
  • Deploy this script as a web app by clicking on “Deploy” -> “New deployment”. Set the access to “Anyone” and deploy.

2. Client-Side: HTML with JavaScript

Next, create an HTML file that fetches this data when the web app is loaded. This HTML file should include JavaScript that calls the Apps Script web app URL.

<!DOCTYPE html>
<html>
<head>
    <title>Spreadsheet Data</title>
</head>
<body>
    <h1>Spreadsheet Data</h1>
    <button onclick="fetchData()">Load Data</button>
    <pre id="output"></pre>

    <script>
        function fetchData() {
            const url = 'WEB_APP_URL_HERE'; // Replace WEB_APP_URL_HERE with the URL of your published web app
            
            fetch(url)
                .then(response => response.json())
                .then(data => {
                    document.getElementById('output').textContent = JSON.stringify(data, null, 2);
                })
                .catch(error => console.error('Error fetching data:', error));
        }
    </script>
</body>
</html>

Instructions for Use:

  1. Replace WEB_APP_URL_HERE in the HTML code with the actual URL of your deployed web app.
  2. You can host this HTML on any static file server, or simply open it directly in a browser (if running locally and the browser allows fetching local files).

What This Does:

  • The Apps Script function doGet serves the content of the active sheet as a JSON string.
  • The HTML page includes a button that, when clicked, fetches the JSON data from the Google Apps Script web app and displays it on the page.

This setup provides a basic example of how to link Google Sheets data with a web page using Google Apps Script. For more complex applications, consider handling authentication, error checking, and data processing both on the server and client sides.