|
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; |
|
})(); |
Sheet
https://docs.google.com/spreadsheets/d/1927NAILJ9_CSkhSYmdxB-kUBN_7Yx6ZN5GXqyK6tKbY/edit#gid=308685413
Overall Sheet Data
https://spreadsheets.google.com/feeds/worksheets/1927NAILJ9_CSkhSYmdxB-kUBN_7Yx6ZN5GXqyK6tKbY/public/values?alt=json-in-script&callback=console.log
Tab #1 Data
https://spreadsheets.google.com/feeds/cells/1927NAILJ9_CSkhSYmdxB-kUBN_7Yx6ZN5GXqyK6tKbY/1/public/values?alt=json-in-script&callback=console.log