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

  1. Go to Google Sheets and create a new spreadsheet.
  2. Add some sample data. For example:
NameAgeEmail
John Doe30john@example.com
Jane Doe25jane@example.com
Mark Lee22mark@example.com
  1. Rename the sheet to something simple, like Sheet1.

Step 2: Create a New Google Apps Script Project

  1. Open Google Apps Script.
  2. Click + New Project and name it something like Display Spreadsheet Data.
  3. 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 using getDataRange() and getValues().

Step 3: Create the HTML File

  1. In your Apps Script editor, click on the + icon to create a new file and select HTML.
  2. Name it index.html.
  3. 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 the getSpreadsheetData() function in Apps Script.
  • Uses displayData() to format the returned data into an HTML table and display it inside the data <div>.

Step 4: Serve the HTML Page

Now, you need to serve the HTML page so that users can access it.

  1. 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

  1. In the Apps Script editor, click on Deploy > Test deployments > Web app.
  2. Set Execute as to Me.
  3. Set Who has access to Anyone (or restrict it based on your needs).
  4. 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.

Next Post Previous Post
No Comment
Add Comment
comment url