thanh01pmt · December 24, 2020 23:42 · Aug 7, 2018 · Dec 18, 2017 · Dec 18, 2017 · Dec 18, 2017
diff --git a/Code.gs → code.gs b/Code.gs → code.gs
diff --git a/README.md b/README.md
@@ -3,6 +3,7 @@
 If you're trying to do this, you came to the right place!
 
 Watch this code work in real time:  <https://twitter.com/CodingDoug/status/942576182276497409>
+
 See also this gist for copying in the other direction: https://gist.github.com/CodingDoug/ffc4f050cc489a0280eb7f4cbe36af07
 
 ## Setup

diff --git a/README.md b/README.md
@@ -2,7 +2,8 @@
 
 If you're trying to do this, you came to the right place!
 
-Watch this code work in real time: <https://twitter.com/CodingDoug/status/940022568089554944>
+Watch this code work in real time:  <https://twitter.com/CodingDoug/status/942576182276497409>
+See also this gist for copying in the other direction: https://gist.github.com/CodingDoug/ffc4f050cc489a0280eb7f4cbe36af07
 
 ## Setup
 

diff --git a/README.md b/README.md
@@ -2,6 +2,8 @@
 
 If you're trying to do this, you came to the right place!
 
+Watch this code work in real time: <https://twitter.com/CodingDoug/status/940022568089554944>
+
 ## Setup
 
 These instructions assume that you already have a Firebase project, and you're working with a spreadsheet in Google Drive under the same account.

diff --git a/Code.gs b/Code.gs
@@ -0,0 +1,45 @@
+// Copyright 2017 Google LLC.
+//
+// Licensed under the Apache License, Version 2.0 (the "License");
+// you may not use this file except in compliance with the License.
+// You may obtain a copy of the License at
+//
+// https://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing, software
+// distributed under the License is distributed on an "AS IS" BASIS,
+// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+// See the License for the specific language governing permissions and
+// limitations under the License.
+
+function myOnEdit(e) {
+    const RTDB_URL = "https://your-project.firebaseio.com"
+
+    const sheet = e.range.getSheet()
+    const range = sheet.getDataRange()
+    const allValues = range.getValues()
+
+    // Get column indexes from the names of the headers in the first row
+    const headers = {}
+    allValues[0].forEach(function(value, index) {
+        headers[value] = index
+    })
+
+    // Collect all the data from the sheet into a object to send to the database
+    const dbData = {}
+    allValues.forEach(function(row, index) {
+        if (index === 0) { return }  // skip header row
+        dbData[row[headers.item_id]] = {
+            qty: row[headers.qty],
+            purchased: row[headers.purchased]
+        }
+    })
+
+    const token = ScriptApp.getOAuthToken()
+    const url = RTDB_URL + "/items.json?access_token=" + encodeURIComponent(token)
+    const response = UrlFetchApp.fetch(url, {
+        method: 'put',
+        payload: JSON.stringify(dbData)
+    })
+    Logger.log(response.getResponseCode())
+}
diff --git a/README.md b/README.md
@@ -0,0 +1,46 @@
+# Copying Data from a Google Sheet into Firebase Realtime Database in real time via Apps Script
+
+If you're trying to do this, you came to the right place!
+
+## Setup
+
+These instructions assume that you already have a Firebase project, and you're working with a spreadsheet in Google Drive under the same account.
+
+1. Create a new Google Sheet
+
+2. Add three headers to the first row with the text "item_id", "qty", and "purchased"
+
+3. Open up the script editor for this sheet with `Tools -> Script editor...`
+
+4. Give this project a name by clicking the text at the very top.
+
+5. Open up the manifest with View -> Show manifest file
+
+6. Add the necessary scopes to the top level of the manifest JSON.  These are required to use the Firebase Realtime Database REST API.  The final manifest will looks like the manifest file added to this gist with the file name `appscript.json`.
+    - https://www.googleapis.com/auth/userinfo.email
+	- https://www.googleapis.com/auth/firebase.database
+	- https://www.googleapis.com/auth/script.external_request
+
+7. Switch back to the main script file `Code.gs` and paste the code from this gist into it.  It defines a trigger function called `myOnEdit`.  Change the value of `RTDB_URL` to your Realtime Database URL.
+
+8. Install the trigger into this project.
+
+    1. `Edit -> Current Project's triggers`
+	2. Click the link to add one.
+	3. Configure it to show "myOnEdit", "From spreadsheet", "On Edit"
+	4. Click Save
+
+9. You will have to authorize this app to ask you for permission to use the OAuth scopes from earlier.  This involves bypassing a warning dialog.  Choose "Advanced", follow the instructions, select your Google Account, and authorize the permissions.
+
+    - **Note**: this processes authorizes your Google account to use the REST API to make changes to your Realtime Database in the same project.  If you share this project with others, this trigger will not be installed, and you will have to perform additional steps to allow that other account to make REST calls.
+
+10. Add rows to the spreadsheet.  You may only use valid Realtime Database keys for item_id.  qty is intended to hold a number, and purchase to hold a boolean "true" or "false".  These rows should appear as nodes in your database.
+
+If for some reason things don't work, check the execution transcript for errors. `View -> Execution transcript`
+
+## Helpful documentation
+
+- <https://developers.google.com/apps-script/guides/triggers/installable>
+- <https://developers.google.com/apps-script/reference/script/>
+- <https://developers.google.com/apps-script/concepts/scopes#setting_explicit_scopes>
+- <https://firebase.google.com/docs/database/rest/auth>
diff --git a/appscript.json b/appscript.json
@@ -0,0 +1,11 @@
+{
+  "timeZone": "America/Los_Angeles",
+  "dependencies": {
+  },
+  "exceptionLogging": "STACKDRIVER",
+  "oauthScopes": [
+    "https://www.googleapis.com/auth/userinfo.email",
+    "https://www.googleapis.com/auth/firebase.database",
+    "https://www.googleapis.com/auth/script.external_request"
+  ]
+}
No results found