Last active
August 8, 2019 21:54
-
-
Save pianosnake/bb4d233900aec52ec3ce1ccc1fcf85b7 to your computer and use it in GitHub Desktop.
Web component to inline a value from a Google Sheet into your HTML page. Use the component by giving it a value of the cell you'd like to display <g-cell value='a2'></g-cell> or if you need a cell from a different page of the sheet provide that number first followed by an exclamation mark <g-cell value='2!a2'></g-cell>
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| // define window.sheetId sometime before loading this script. the sheet id is in the URL of the Google Sheet when you're editing it. | |
| if(!window.sheetId) throw Error('window.sheetId required'); | |
| class DataProvider { | |
| constructor(sheetId) { | |
| this.sheetId = sheetId; | |
| this.dataPromises = {}; | |
| } | |
| fetchJSON(sheetNumber) { | |
| const jsonURL = `https://spreadsheets.google.com/feeds/cells/${this.sheetId}/${sheetNumber}/public/full?alt=json`; | |
| return new Promise(async resolve => { | |
| const response = await fetch(jsonURL); | |
| const data = await response.json(); | |
| resolve(data.feed.entry); | |
| }) | |
| } | |
| getSheetData(sheetNumber) { | |
| if (!this.dataPromises[sheetNumber]) { | |
| this.dataPromises[sheetNumber] = this.fetchJSON(sheetNumber); | |
| } | |
| return this.dataPromises[sheetNumber]; | |
| } | |
| } | |
| const dp = new DataProvider(window.sheetId); | |
| class GoogleSheetCell extends HTMLElement { | |
| constructor() { | |
| super(); | |
| this.attachShadow({ mode: 'open' }); | |
| } | |
| connectedCallback() { | |
| dp.getSheetData(this.sheetNumber).then(entries => { | |
| const cell = entries.find(entry => { | |
| return entry.title['$t'].toLowerCase() === this.cellId.toLowerCase(); | |
| }) | |
| if (cell) this.shadowRoot.innerHTML = cell.content['$t']; | |
| }) | |
| } | |
| get sheetNumber() { | |
| if (this.value.includes('!')) { | |
| return this.value.split('!')[0]; | |
| } else { | |
| // default to sheet 1 if no sheet number is given | |
| return 1; | |
| } | |
| } | |
| get cellId() { | |
| if (this.value.includes('!')) { | |
| return this.value.split('!')[1]; | |
| } else { | |
| return this.value; | |
| } | |
| } | |
| get value(){ | |
| return this.getAttribute('value'); | |
| } | |
| } | |
| customElements.define('g-cell', GoogleSheetCell); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment