Topics
Data Tables - How to show data from Google Spreadsheet or API?
The ScreenManager DataTable App is a powerful tool that allows businesses to dynamically display data on their digital signage screens. By leveraging external data sources, including Google Spreadsheets or any other API (Application Interface), this feature ensures that the displayed information is always current and relevant. Here’s a closer look at how businesses can benefit from this functionality.
How to load data into Data Table from your Google Spreadsheet
Imagine needing to integrate spreadsheet data into ScreenManager, leveraging your existing Google Spreadsheets. Here’s a simple solution: create your own API from your Google Spreadsheet using Google Apps Script. The best part? You don't need a paid G-Suite account; it works with free Gmail accounts too.
Google App Scripts are like Excel macros for Google Spreadsheets. They allow you to write JavaScript functions to interact with your spreadsheet programmatically and expose them to the internet. You can give access to this Google Apps Script URL to load the data from your spreadsheets directly into ScreenManager's Data Table app.
1. Create your Google Spreadsheet
Something like this. Here we have a simple Google Spreadsheet with craft beers:
2. Create Google Apps Script
To create API compatible with ScreenManager, we need the JavaScript code below. If you do not understand JavaScript, contact our customer support for help.
This code takes your spreadsheet and transforms it into a JSON Array which ScreenManager understands and can load the data from it.
// Global variables
var SHEET_NAME = 'Sheet1'; // Change this to the name of your sheet
var KEYS_ROW = 1; // The row containing the keys
var FIRST_DATA_ROW = 2; // The first row containing the data
// Function to handle doGet requests
function doGet(e) {
try {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
if (!sheet) {
throw new Error('Sheet not found: ' + SHEET_NAME);
}
var keys = sheet.getRange(KEYS_ROW, 1, 1, sheet.getLastColumn()).getValues()[0]; // Get all keys from the first row
var lastRow = sheet.getLastRow();
var dataRange = sheet.getRange(FIRST_DATA_ROW, 1, lastRow - FIRST_DATA_ROW + 1, sheet.getLastColumn()); // Get data from all columns
var dataValues = dataRange.getValues();
var data = [];
for (var i = 0; i < dataValues.length; i++) {
var row = {};
for (var j = 0; j < keys.length; j++) {
row[keys[j]] = dataValues[i][j];
}
data.push(row);
}
return ContentService.createTextOutput(JSON.stringify(data)).setMimeType(ContentService.MimeType.JSON);
} catch (error) {
Logger.log('Error: ' + error.message);
return ContentService.createTextOutput(JSON.stringify({ error: error.message })).setMimeType(ContentService.MimeType.JSON);
}
}
Code Explanation
Global Variables
- SHEET_NAME: The name of your sheet (default is something like Sheet1)
- KEYS_ROW: The row number containing the keys (set to the first row)
- FIRST_DATA_ROW: The first row containing the data (set to the second row)
Function doGet
- Sheet Validation: Checks if the specified sheet exists.
- Keys Retrieval: Gets all keys from the first row, spanning the entire column range.
- Data Retrieval: Gets all data starting from the second row, spanning the entire column range.
- Data Processing: Loops through the data and creates objects for each row, using the keys from the first row.
Response: Returns the data as a JSON array.
Create the Google Apps Script API
So how to create an app script? You need to be the owner of spreadsheet or at least editor with full authorization from the owner of the spreadsheet. Just click on Extensions > App Script
You will get this screen with a text area where you replace javascript code with one you saw up.
So now the app script should look like in the image below.
After that you just click on the blue button “Deploy” and select “New deployment”. Then you get a modal where you have to set New deployment as a “Web app”.
Then you should add a description to your deployment and have to set access for Anyone (very important, otherwise the API won't be visible in ScreenManager and no one but you).
On first deployment app scripts need authorization from you, so you must approve it.
After authorization of the app script you will get a Web App URL for your new API. You just need to save this url for later.
Congratulations. You've just set up a new API from Google Sheets using an app script. Now it's time to connect your API to ScreenManager. So create a new Scene or App with a Data Table.
4. Load data from your Google Apps Script into Data Table app
Now you have to put your Web App URL of the API you saved before to the Data Table by clicking the button “Edit the data source”.
On this step you see what data your API returns to ScreenManager. You can also edit column titles. Do not modify the "Data Key" since it identifies from which column the data should be extracted from.
Also you can drag and drop columns to change their order.
Now you have successfully connected your API to ScreenManager and it is time to set the refresh interval of the data and number of rows you want to present.
Also you can offset some rows if you want to create paginated views of Data Tables. With this, you can easily create two Data Tables next to each other, one of them showing first 10 rows and the second one following 10 rows by settings the "offset" to 10 and the "number of rows to show" to 10.
5. Make your Data Table look more beautiful
You also can change the appearance of the Data Table like colors, fonts, borders, spacing, etc.
Now you can publish the created Scene or App to your digital signage screens and share information with your customers or colleagues. You can also mix the Data Table with another media such as videos, images or another app to make it more attractive.
The final result may look like this:
We hope that you will find Data Tables useful. In case that you have any questions or need to integrate different APIs than Google Apps Script, get in touch with us and we will find the optimal solution for you.
