Last active
May 25, 2021 19:10
-
-
Save dottedsquirrel/b248e5098fe2a9c860ed60f7db7c7e1d to your computer and use it in GitHub Desktop.
Code.gs - Spreedsheet to Magic API
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
{ | |
"timeZone": "America/Los_Angeles", | |
"dependencies": { | |
"libraries": [{ | |
"userSymbol": "FirebaseApp", | |
"libraryId": "1hguuh4Zx72XVC1Zldm_vTtcUUKUA6iBUOoGnJUWLfqDWx5WlOJHqYkrt", | |
"version": "29", | |
"developmentMode": true | |
}] | |
}, | |
"exceptionLogging": "STACKDRIVER", | |
"oauthScopes": ["https://www.googleapis.com/auth/firebase.database", "https://www.googleapis.com/auth/userinfo.email", "https://www.googleapis.com/auth/spreadsheets", "https://www.googleapis.com/auth/script.scriptapp", "https://www.googleapis.com/auth/script.external_request"], | |
"executionApi": { | |
"access": "DOMAIN" | |
} | |
} |
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
/** | |
* Copyright 2019 Google LLC. | |
* SPDX-License-Identifier: Apache-2.0 | |
*/ | |
function getEnvironment() { | |
var environment = { | |
spreadsheetID: "<REPLACE WITH YOUR SPREADSHEET ID>", | |
firebaseUrl: "<REPLACE WITH YOUR REALTIME DB URL>" | |
}; | |
return environment; | |
} | |
// Creates a Google Sheets on change trigger for the specific sheet | |
function createSpreadsheetEditTrigger(sheetID) { | |
var triggers = ScriptApp.getProjectTriggers(); | |
var triggerExists = false; | |
for (var i = 0; i < triggers.length; i++) { | |
if (triggers[i].getTriggerSourceId() == sheetID) { | |
triggerExists = true; | |
break; | |
} | |
} | |
if (!triggerExists) { | |
var spreadsheet = SpreadsheetApp.openById(sheetID); | |
ScriptApp.newTrigger("importSheet") | |
.forSpreadsheet(spreadsheet) | |
.onChange() | |
.create(); | |
} | |
} | |
// Delete all the existing triggers for the project | |
function deleteTriggers() { | |
var triggers = ScriptApp.getProjectTriggers(); | |
for (var i = 0; i < triggers.length; i++) { | |
ScriptApp.deleteTrigger(triggers[i]); | |
} | |
} | |
// Initialize | |
function initialize(e) { | |
writeDataToFirebase(getEnvironment().spreadsheetID); | |
} | |
// Write the data to the Firebase URL | |
function writeDataToFirebase(sheetID) { | |
var ss = SpreadsheetApp.openById(sheetID); | |
SpreadsheetApp.setActiveSpreadsheet(ss); | |
createSpreadsheetEditTrigger(sheetID); | |
var sheets = ss.getSheets(); | |
for (var i = 0; i < sheets.length; i++) { | |
importSheet(sheets[i]); | |
SpreadsheetApp.setActiveSheet(sheets[i]); | |
} | |
} | |
// A utility function to generate nested object when | |
// given a keys in array format | |
function assign(obj, keyPath, value) { | |
lastKeyIndex = keyPath.length - 1; | |
for (var i = 0; i < lastKeyIndex; ++i) { | |
key = keyPath[i]; | |
if (!(key in obj)) obj[key] = {}; | |
obj = obj[key]; | |
} | |
obj[keyPath[lastKeyIndex]] = value; | |
} | |
// Import each sheet when there is a change | |
function importSheet() { | |
var sheet = SpreadsheetApp.getActiveSheet(); | |
var name = sheet.getName(); | |
var data = sheet.getDataRange().getValues(); | |
var dataToImport = {}; | |
for (var i = 1; i < data.length; i++) { | |
dataToImport[data[i][0]] = {}; | |
for (var j = 0; j < data[0].length; j++) { | |
assign(dataToImport[data[i][0]], data[0][j].split("__"), data[i][j]); | |
} | |
} | |
var token = ScriptApp.getOAuthToken(); | |
var firebaseUrl = | |
getEnvironment().firebaseUrl + sheet.getParent().getId() + "/" + name; | |
var base = FirebaseApp.getDatabaseByUrl(firebaseUrl, token); | |
base.setData("", dataToImport); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment