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:
- Replace
WEB_APP_URL_HERE
in the HTML code with the actual URL of your deployed web app. - 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.
