Skip to content

Instantly share code, notes, and snippets.

@westc
Last active August 22, 2021 01:57
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save westc/68af804d4063061095ab348047e150b1 to your computer and use it in GitHub Desktop.
Save westc/68af804d4063061095ab348047e150b1 to your computer and use it in GitHub Desktop.
Provides an easy way to get Google Sheets data.

GoogleSheetReader

Provides the ability to read the contents of published Google sheets with ease!

Example Run

var url = 'https://docs.google.com/spreadsheets/d/1927NAILJ9_CSkhSYmdxB-kUBN_7Yx6ZN5GXqyK6tKbY/edit#gid=308685413';
var apiKey = 'YOUR-API-KEY';
var gsr = new GoogleSheetReader(url, apiKey);
gsr.onReady(function() {
  this.loadAllTabs(function() {
    this.getTabTitles().forEach(function(tabTitle) {
      console.log(tabTitle, this.getTabRowObjects(tabTitle));
    }, this);
  });
});
var GoogleSheetReader = (function () {
const BASE_API_URL = 'https://sheets.googleapis.com/v4/spreadsheets/';
function loadJSON(url, callback) {
const xhr = new XMLHttpRequest();
xhr.onabort = xhr.onerror = function() {
callback.call(xhr, false);
};
xhr.onload = function() {
callback.call(xhr, true, JSON.parse(xhr.responseText));
};
xhr.open(
'GET',
url
// (url + '?cache-buster=' + Math.random()).replace(/(\?.+)\?/, '$1&')
);
xhr.send();
}
function GoogleSheetReader(spreadsheetIdOrUrl, apiKey) {
function fireReadyHandlers() {
self.isReady = true;
hiddenKeys.forEach(function (key) {
delete self[key];
});
if (hadPrematureCalls) {
console.log("Now this GoogleSheetReader's functions can be called:", self);
}
self.readyHandlers.forEach(function (readyHandler) {
readyHandler.call(self);
});
delete self.readyHandlers;
}
const self = this;
let hadPrematureCalls;
self.isReady = false;
self.readyHandlers = [];
self.errorHandlers = [];
var hiddenKeys = Object['keys'](GoogleSheetReader.prototype).filter(function (key) {
var keepKey = !/^on(Error|Ready)$/.test(key);
if (keepKey) {
self[key] = function () {
hadPrematureCalls = true;
throw new Error('The "' + key + '" function cannot be executed yet.');
};
}
return keepKey;
});
const spreadsheetId = GoogleSheetReader.parseSheetId(spreadsheetIdOrUrl);
if (!spreadsheetId) {
throw new Error('Either a URL or a Google Sheet ID must be specified.');
}
loadJSON(
BASE_API_URL + spreadsheetId + '?key=' + apiKey,
function (success, data) {
if (self.hasErrors = !success) {
self.errorHandlers.forEach(function (errorHandler) {
errorHandler.call(self);
});
}
else {
assign(self, {
apiKey,
spreadsheetId: data.spreadsheetId,
spreadsheetUrl: data.spreadsheetUrl,
title: data.properties.title,
timeZone: data.properties.timeZone,
locale: data.properties.locale,
apiData: data,
tabs: data.sheets.map(sheet => ({
hasLoaded: false,
sheetId: sheet.properties.sheetId,
title: sheet.properties.title,
apiData: sheet,
})),
});
fireReadyHandlers();
}
}
);
}
function assign(objToMod) {
for (var args = arguments, i = 1, l = args.length; i < l; i++) {
var objSource = args[i];
for (var k in objSource) {
if (Object.prototype.hasOwnProperty.call(objSource, k)) {
objToMod[k] = objSource[k];
}
}
}
return objToMod;
}
assign(GoogleSheetReader, {
parseSheetId: function (url) {
return (/^(?:https\:\/\/[^\?#]+\/d\/)?([\w\-]{20,})(?:[\/\?\#]|$)/.exec(url) || [])[1];
},
parseExcelDate: function (num) {
return new Date((num - 25569) * 864e5 + (new Date).getTimezoneOffset() * 6e4);
},
isValidUrl: function (url) {
return !!GoogleSheetReader.parseSheetId(url);
},
/**
* @see https://gist.github.com/westc/ea154cab93336999968ece2fe6f629e1
* Takes an integer and returns the corresponding column name (eg. 5 becomes E).
* @param {number} number
* The integer to convert to a column name. If `opt_isZeroBased` is true
* then 0 will be converted to "A", otherwise 1 will be converted to "A".
* @param {?boolean=} opt_isZeroBased
* Indicates if `number` is interpreted as a 0-based index.
* @return {string}
* The column name.
*/
toColumnName: function (number, opt_isZeroBased) {
for (
var index, num = number + (opt_isZeroBased ? 1 : 0), ret = '';
index = num - 1, num;
num = Math.floor(index / 26)
) {
ret = String.fromCharCode(65 + index % 26) + ret;
}
return ret;
},
/**
* @see https://gist.github.com/westc/c8a08042d176600850a5e5cbc4c226e9
* Takes a column name and returns the corresponding integer (eg. E becomes 5).
* @param {string} columnName
* The column name (eg. A, B, C, ..., Z, AB, AC, etc.) to be converted to an
* integer.
* @param {?boolean=} opt_isZeroBased
* Indicates if the returned number should be 0-based. If 0-based "E" will
* become 4.
* @return {number}
* The integer representing the column name.
*/
toColumnNumber: function (columnName, opt_isZeroBased) {
for (var number = 0, i = columnName.length, j = 0; i--; j++) {
number += Math.pow(26, i) * (columnName.charCodeAt(j) - 64);
}
return number - (opt_isZeroBased ? 1 : 0);
},
parseRows: function (values) {
const rows = [];
for (let valueIndex = 0, valueCount = values.length; valueIndex < valueCount; valueIndex++) {
const row = [];
let value = values[valueIndex]
for (let valueIndex = 0, valueCount = value.length; valueIndex < valueCount; valueIndex++) {
let cellValue = value[valueIndex];
if (/^-?\d+(\.\d+)?$/.test(cellValue)) {
cellValue = +cellValue;
}
else if (cellValue.numericValue && /\b(?:\d\d?(\/|\-)\d\d?(?:\1\d{4})?|\d{4}(\/|\-)\d\d?\2\d\d?)\b/.test(cellValue)) {
cellValue = GoogleSheetReader.parseExcelDate(cellValue.numericValue);
}
else if (cellValue === 'TRUE') {
cellValue = true;
}
else if (cellValue === 'FALSE') {
cellValue = false;
}
row.push(cellValue);
}
rows.push(row);
}
return rows;
}
});
GoogleSheetReader.prototype = {
constructor: GoogleSheetReader,
getTabIndex: function (indexOrName, opt_throwErrors) {
let index = indexOrName;
const count = this.tabs.length;
const isNumber = 'number' === typeof indexOrName;
if (isNumber) {
index = indexOrName < 0 ? (count + ~~indexOrName) % count : ~~indexOrName;
}
else {
indexOrName = indexOrName.toUpperCase();
for (index = this.tabs.length; index--; ) {
if (this.tabs[index].title.toUpperCase() === indexOrName) {
break;
}
}
}
if (opt_throwErrors) {
if (index < 0 || index >= count) {
throw new Error(isNumber ? 'There is no tab at index ' + indexOrName + '.' : ('There is no tab named "' + indexOrName + '".'));
}
else {
var tab = this.tabs[index];
if (!tab.hasLoaded && opt_throwErrors !== -1) {
throw new Error('The tab named "' + tab.title + '" has not been loaded yet.');
}
}
}
return index;
},
getTabTitles: function () {
return this.tabs.map(function (tab) {
return tab.title;
});
},
getTabTitle: function (indexOrName) {
return this.tabs[this.getTabIndex(indexOrName, true)].title;
},
getTabData: function (indexOrName) {
return this.tabs[this.getTabIndex(indexOrName, true)].feed;
},
getTabRowValues: function (indexOrName, opt_ignoreTypes) {
return opt_ignoreTypes
? this.tabs[this.getTabIndex(indexOrName, true)].apiData.values
: this.tabs[this.getTabIndex(indexOrName, true)].rows;
},
getTabRowObjects: function (indexOrName, options) {
var rowValues = this.getTabRowValues(indexOrName, Object(options).ignoreTypes);
var realLastRowIndex = rowValues.length - 1;
options = assign(
{
headerIndex: 0,
lastRowIndex: realLastRowIndex,
endOnEmptyRow: true
},
options
);
var lastRowIndex = options.lastRowIndex;
if (lastRowIndex < 0) {
lastRowIndex += realLastRowIndex;
}
var headerIndex = options.headerIndex;
rowValues = rowValues.slice(Math.max(headerIndex, 0), lastRowIndex + 1);
if (headerIndex < 0 && rowValues.length) {
var headerRow = rowValues[0].map(function(v, i) {
return GoogleSheetReader.toColumnName(i, true);
});
}
else {
var headerRow = rowValues.shift(0);
}
if (options.endOnEmptyRow) {
rowValues.some(function (row, rowIndex) {
if (!row.length) {
return rowValues = rowValues.slice(0, rowIndex);
}
});
}
return new Array(rowValues.length)
.join(' ')
.split(' ')
.map(function (_, rowIndex) {
return headerRow.reduce(
function (objRow, colName, colIndex) {
var arrRow = rowValues[rowIndex];
objRow[colName] = arrRow && arrRow[colIndex];
return objRow;
},
{}
);
});
},
getTabCount: function () {
return this.tabs.length;
},
onReady: function (readyHandler) {
if (this.isReady) {
readyHandler.call(this);
}
else {
this.readyHandlers.push(readyHandler);
}
},
onError: function (errorHandler) {
if (this.hasErrors) {
readyHandler.call(this);
}
else {
this.errorHandlers.push(errorHandler);
}
},
loadTab: function (indexOrName, callback) {
var index = this.getTabIndex(indexOrName, -1);
var tab = this.tabs[index];
if (tab.hasLoaded) {
callback.call(this, index);
}
else {
const {title} = tab;
const url = BASE_API_URL
+ this.spreadsheetId
+ "/values/'"
+ encodeURIComponent(title.replace(/'/g, "''"))
+ "'!A:ZZZ?key="
+ this.apiKey;
loadJSON(url, function (success, apiData) {
assign(tab, {
apiData,
hasLoaded: true,
rows: GoogleSheetReader.parseRows(apiData.values)
});
callback.call(this, index);
});
}
},
loadAllTabs: function (opt_callback) {
const self = this;
let tabLoadCount = 0;
const tabCount = self.tabs.length;
self.tabs.forEach(function (tab, index) {
if (!tab.hasLoaded) {
self.loadTab(index, function() {
if (tabCount === ++tabLoadCount && opt_callback) {
opt_callback.call(self);
}
});
}
});
if (tabCount === tabLoadCount && opt_callback) {
opt_callback.call(self);
}
},
getReadyCount: function () {
return this.tabs.filter(function (tab, index) {
return tab.hasLoaded;
}).length;
},
}
'spreadsheetId spreadsheetUrl title timeZone locale apiData'.replace(
/(\w)(\w+)/g,
function (name, first, rest) {
GoogleSheetReader.prototype['get' + first.toUpperCase() + rest] = function () {
return this[name];
};
}
);
return GoogleSheetReader;
})();
// HIDE \\
console.load('local://GoogleSheetReader.js');
//\\
var url = 'https://docs.google.com/spreadsheets/d/1927NAILJ9_CSkhSYmdxB-kUBN_7Yx6ZN5GXqyK6tKbY/edit#gid=308685413';
var gsr = new GoogleSheetReader(url);
gsr.onReady(function() {
this.loadAllTabs(function() {
this.getTabTitles().forEach(function(tabTitle) {
console.log(tabTitle, this.getTabRowObjects(tabTitle));
}, this);
});
});