Using Google Spreadsheet as a Remote Database for your Crossrider Extension
Often extensions employ a remote database for storing/collating data. Setting up and managing such databases can be time consuming, costly, and an undesireable overhead. Google Spreadsheet provides a convenient and flexible JavaScript Service API that can utilize a spreadsheet as a remote database.
This blog walks you through the steps required to create, configure, and code a simple Crossrider extension that counts page visits using a spreadsheet as a database.
Let's start by creating a Google Spreadsheet:
Create a new Spreadsheet in your Google Docs account (Sheets > Create new spreadsheet) and optionally give it a name (e.g. Blog Database).
Add the required column headings (e.g. URL, Count) and format the column cells as plain text (Select the columns > Format > Number > Plain text).
Make a note of the sheet's Alphanumeric ID. You can get the id from the sheet's URL:
In the next step, we add and deploy the sheet's server-side script:
Create the server-side script (Tools > Script editor).
In the editor, add the following code for handling HTTP GET requests (for more information, see Web Apps and Google Sites Gadgets and Spreadsheet Service:
// Global vars var actions = { getUrlCount: getUrlCount, incUrlCount: incUrlCount }, sheetId = '', // Specify the SHEET_ID here data = {}; /** * Handles API requests received via the HTTP GET method/verb * @param {object} request * @return {JSON} res */ function doGet(request) { // return if action is not specified var action = request.parameter.action; if (!action) return ContentService.createTextOutput(JSON.stringify({ action: action, error: 'Missing parameter: action not specified' })) .setMimeType(ContentService.MimeType.JSON); // get URL of visited site and initialize response var url = request.parameter.url, res = { action: action, count: 0 }; // process if action function exists if (typeof actions[action] == "function") { // set sheet information data.sheet = SpreadsheetApp.openById(sheetId).getSheets()[0]; data.cells = data.sheet.getDataRange().getValues(); // set response count res.count = actions[action]("" + url); } return ContentService.createTextOutput(JSON.stringify(res)) .setMimeType(ContentService.MimeType.JSON); } // Helper functions /** * Searches sheet for the row containing the specified * @param {String} url * @return {Number} row */ function findRow(url) { // Iterate through the rows and find matching url for (var i=1; i<data.cells.length; i++) { if (data.cells[i][0] == url) { return i; } } return null; } /** * Gets the URL visits count from sheet * @param {String} url * @return {Number} count */ function getUrlCount(url) { var row=findRow(url); return (row) ? data.cells[row][1] : 0; } /** * Increments visit count for specified url * @param {String} url * @return {Number} count */ function incUrlCount(url) { // Find row containing url var row = findRow(url); // url does not exist, append new row and initialize count to 1 if (!row) { data.sheet.appendRow([url, 1]); return 1; } // url exists // Get current visit count and its sheet cell var count = data.cells[row][1], cell = data.sheet.getRange("B" + (parseInt(row, 10) + 1)); // Increment visit count cell.setValue(++count); return count; }
Deploy the script as a web app (Publish > Deploy as web app) with the following settings:
Who has access to the app: Anyone, even anonymous
NOTE: To test each change you make to the script, save the script (File > Save), increment the version (File > Manage versions), and deploy it.
Make a note of the script's Alphanumeric ID. You can get the id from the script's URL:
NOTE: Google Services implements limits on the number of API calls. For more information, see Quotas for Google Services.
Finally, we create a Crossrider extension that uses the deployed web app. The code for this example, is comprised of the following: (a) code for incrementing the page visit count, and (b) code for displaying the current page visit count. The extension simply runs on each page, sends a request to increment the visit count, and then displays a banner showing the global number of page visits for 3 seconds.
Start by creating a new extension (My Extensions > Create New Extension),
and then edit the code (Edit Code > extension.js).
In the editor, add the following code:
appAPI.ready(function($) { // Execuet script API URL without parameters var execUrl = "https://script.google.com/macros/s/SCRIPT_ID/exec"; // Specify the SCRIPT_ID here /** * Defines functions that use the Web App script: * @param {String} action * @return {Number} function(url, onSuccess, onFailure) * - url: url visited. Defaults to current page URL * - onSuccess: callback function if request succeeds * - onFailure: callback function if request fails */ function defineFn (action) { // return if action is not specified if (!action) return; return function (options) { // Set options url = options.url || window.location.href; onSuccess = options.onSuccess || function (){}; onFailure = options.onFailure || function (){}; /** * onSuccessWrapper: wrapper to parse response data * requestUrl: API URL with parameters applied */ var onSuccessWrapper = function (data) { try { onSuccess(appAPI.JSON.parse(data)); } catch (e) { onSuccess(null); } }, requestUrl = execUrl + "?action=" + action + "&url=" + url; // Make GET request appAPI.request.get({ url: requestUrl, onSuccess: onSuccessWrapper, onFailure: onFailure }); }; } // Define increment visit count function var incUrlCount = defineFn("incUrlCount"); // Optional: Define get visit count function //var getUrlCount = defineFn("getUrlCount"); // Inject notification HTML into page $('') .css({ 'background-color':'#000', 'color':'#fff', 'font-size':'12px', 'height':'20px', 'line-height':'20px', 'position':'fixed', 'text-align':'center', 'top':'-20px', // Hide notification 'width':'100%', 'z-index':'99999' }) .appendTo('body'); // Increment the page visit count and then display notification incUrlCount({ url: location.origin + location.pathname, // current page URL onSuccess: function(response) { if (response.count) { // Display notification if increment succeeded $('body').animate({'margin-top': ['20px', 'swing']}); $('#gsdb-demo') .html('This page has been visited '+response.count+' time'+((response.count!==1)?'s':'')) .animate({'top': [0, 'swing']}); appAPI.setTimeout(function() { $('body').animate({'margin-top': [0, 'swing']}); $('#gsdb-demo').animate({'top': ['-20px', 'swing']}); }, 3000); } } }); });
It’s that simple and a significant step forward in developing extensions using remote databases. Give it a try, feel free to improve the code, and see for yourself just how easy it is to use our platform.
Finally, you are invited to try our Google Spreadsheet demo extension and rise to the challenge of making your own cool extensions. Get started quickly by cloning the demo extension (Edit Code > Clone This Extn).
If you are not already a Crossrider developer: click the following link to join our cross-browser extension development framework.
