Apps Script – Get Data From Google Spreadsheet
Google Apps Script makes it easy to automate processes within the Google ecosystem, such as accessing data from Google Sheets and displaying it in a custom web interface. In this tutorial, you'll learn how to fetch data from a Google Spreadsheet and show it in an HTML page using Google Apps Script.
Step 1: Create a New Google Spreadsheet
- Go to Google Sheets and create a new spreadsheet.
- Add some sample data. For example:
Name | Age | |
---|---|---|
John Doe | 30 | john@example.com |
Jane Doe | 25 | jane@example.com |
Mark Lee | 22 | mark@example.com |
- Rename the sheet to something simple, like
Sheet1
.
Step 2: Create a New Google Apps Script Project
- Open Google Apps Script.
- Click + New Project and name it something like
Display Spreadsheet Data
. - In the
Code.gs
file, write the following function to fetch data from the spreadsheet:
Code.gs
function getSpreadsheetData() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1'); // Replace 'Sheet1' with your sheet name
var data = sheet.getDataRange().getValues(); // Get all data from the sheet
return data;
}
This script:
- Fetches the active spreadsheet.
- Retrieves all data from the
Sheet1
usinggetDataRange()
andgetValues()
.
Step 3: Create the HTML File
- In your Apps Script editor, click on the
+
icon to create a new file and select HTML. - Name it
index.html
. - Add the following HTML code to display the spreadsheet data:
index.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<title>Spreadsheet Data</title>
</head>
<body>
<h1>Google Spreadsheet Data</h1>
<div id="data"></div>
<script>
// Call the Apps Script function and display the data
google.script.run.withSuccessHandler(displayData).getSpreadsheetData();
function displayData(data) {
var html = '<table border="1"><tr><th>Name</th><th>Age</th><th>Email</th></tr>';
data.forEach(function(row) {
html += '<tr>';
row.forEach(function(cell) {
html += '<td>' + cell + '</td>';
});
html += '</tr>';
});
html += '</table>';
document.getElementById('data').innerHTML = html;
}
</script>
</body>
</html>
This HTML code:
- Creates an empty
<div>
element where the spreadsheet data will be inserted. - Calls the
google.script.run.getSpreadsheetData()
function, which executes thegetSpreadsheetData()
function in Apps Script. - Uses
displayData()
to format the returned data into an HTML table and display it inside thedata
<div>
.
Step 4: Serve the HTML Page
Now, you need to serve the HTML page so that users can access it.
- In the
Code.gs
file, add the following function to serve the HTML page:
function doGet() {
return HtmlService.createHtmlOutputFromFile('index')
.setTitle('Spreadsheet Data')
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}
This function loads the index.html
file and displays it when the web app is accessed.
Step 5: Deploy the Web App
- In the Apps Script editor, click on Deploy > Test deployments > Web app.
- Set
Execute as
to Me. - Set
Who has access
to Anyone (or restrict it based on your needs). - Click Deploy and copy the web app URL.
Step 6: View the Data in the Web App
Open the URL in your browser. You should see an HTML page displaying the data from your Google Spreadsheet in a table.