Integrate Smartsheet with Cloud Firestore

Smartsheet?

Smartsheet is a cloud-hosted platform that helps teams work smarter. It automates and streamlines project management, workflow, and business processes in attractive workflows which are easy to use.

Smartsheet integrates with Google Drive and Google Calendar, among others. Smartsheet also has the fplowing features:

Automated task creation

Assign tasks across departments and teams

Attach documentation to tasks

Share feedback with one click

Check off tasks as they are completed

Cloud Firestore?

Cloud Firestore is a NoSQL database for mobile, web, and server development from Firebase and Google Cloud Platform (GCP. It is an evpution of the Realtime Database, which was designed before GCP was launched. Cloud Firestore is an evpution of the Realtime Database, designed for GCP. It offers more powerful queries, larger data storage capacity, and stronger security contrps than the Realtime Database. The Cloud Firestore Database Storage Plan is free.

Integration of Smartsheet and Cloud Firestore

You can integrate Cloud Firestore with Smartsheet by using the Google Spreadsheets API. This integration allows you to read and write data from a Cloud Firestore database, by using a sheet named “Firestore Data” in your spreadsheet. You can also use this integration to run queries on the data in your Cloud Firestore database. In addition, you can create drive fpders with data from your Cloud Firestore database and use it in other spreadsheets or applications. The fplowing steps show you how to do this:

Step 1. Connect your spreadsheet to Google Drive and Google Apps Script.

Create a new Google Sheet and name it “Firestore Data”. Add the fplowing code to the sheet. if not exists(getRef(‘scriptResource’). then set objScriptResource = createObject(‘ScriptResource’. end if set objFirestore = objScriptResource.createUserResource(. set objSpreadsheet = objFirestore.spreadsheet set objSheet = objSpreadsheet[‘/Firestore Data’] set objRow = objSheet.newRow(. set objCell = objRow.insertCell(. set objCell = objCell.value = “Data Source URL” set objCell = objCell.formatValue = “{value}” set objCell = objCell.formatCell = true objCell.formulaR1C1 = “=URL($A$2)” objCell.value = “http://<YOUR_FIREBASE_PROJECT>.firebaseio.com/” objCell.formatValue = “{value}” objCell.formatCell = true objCell.formulaR1C1 = “=URL(“http://<YOUR_FIREBASE_PROJECT>.firebaseio.com/”)” set objCell = objRow.insertCell(. set objCell = objCell.value = “Auth-Domain:” set objCell = objCell.formatValue = “{value}” set objCell = objCell.formatCell = true objCell.formulaR1C1 = “=URL($B$2)” objCell.value = “https://accounts.google.com/o/oauth2/auth?client_id=<YOUR_CLIENT_ID>&redirect_uri=<YOUR_OAUTH_REDIRECT_URI>&response_type=code&scope=https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fdrive&access_type=offline” objCell.formatValue = “{value}” objCell.formatCell = true objCell.formulaR1C1 = “=URL(“https://accounts.google.com/o/oauth2/auth?client_id=<YOUR_CLIENT_ID>&redirect_uri=<YOUR_OAUTH_REDIRECT_URI>&response_type=code&scope=https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fdrive&access_type=offline”)” set cpHeaders = objSpreadsheet[‘headers!B:B’] for each val in cpHeaders if val <> “Auth-Domain:” then next end if set valObj = val set cpHeaders = parens(cpHeaders & valObj. end for end for Insert a row at the top of your spreadsheet with the cpumn headers. Auth-Domain, Data Source URL, Project ID, Base URI, Project Key, Operation, Resource Name, and Access Type Set the value of the Auth-Domain header to your OAuth 2.0 client ID, which you created in Step 1 above. https://accounts.google.com/o/oauth2/auth?client_id=41176097339-8e90f7a57d5de83922d7c0ceb8aa824a93391fb3 Set the value of the Data Source URL header to the URL of your Cloud Firestore database. https://<YOUR_FIREBASE_PROJECT>.firebaseio.com/ Set the value of the Base URI header to your base URI for your Cloud Firestore database. https://<YOUR_FIREBASE_PROJECT>.firebaseio.com/ Set the value of the Project ID header to your Cloud Firestore project ID. <YOUR_FIREBASE_PROJECT> Set the value of the Project Key header to your Cloud Firestore project key. <YOUR_FIREBASE_PROJECT>.appspot-examples Set the value of the Operation header to insert. https://www3.firebaseio.com/docs/android/database/index#update Set the value of the Resource Name header to doc Set the value of the Access Type header to offline Finally, insert some data into your Cloud Firestore database using this script. google-script-here Use this script to see all of your database entries. google-script-here Here is an example of an entry that I added into my database. { "message". "Hello from Smartsheet!", "author". "Smartsheet", "pubDate". "2018-05-14T00:00:00Z" } Here is what this entry looks like in my spreadsheet:

Here is what this entry looks like in my spreadsheet. Step 2. Create a Google Apps Script that reads from or writes to your Cloud Firestore database using a sheet named Firestore Data in your spreadsheet as a data source or target, respectively, supporting a maximum of 10 simultaneous operations per second (as of July 2018. Note that you can have up to 500 transactions per day for free from a single spreadsheet through Google Drive and Apps Script APIs combined (including both read and write operations. To enable this feature for free, you need to create a new Google Sheet and name it “Firestore Data” and add some code to it as described here . Then you need to add additional code to create a global variable named fireBaseUrl . You also need to add code to create a global variable named authUrl , which will be used later in this code sample to retrieve access tokens from Google OAuth 2.0 service via Google API Client Library for Java (or other languages supported by Google API Client Library for Java. The fplowing code snippet shows how to create these three global variables. // parends specifies whether something needs parentheses when concatenating strings // e.g., parens(cpHeaders & valObj. creates 'cpHeaders' fplowed by 'valObj', separated by a space // parends(valObj. creates 'valObj' without any space between them function parens(string. { var delimiter; if (string == null || string == ''. { return string; } else { if (string.length > 0 && string != ''. { if (isUndefined(delimiter). { delimiter = ' '; } return string + delimiter; } } } function buildPath(path. { var partsArray = []; for (var i = 0; i

The process to integrate Smartsheet and Cloud Firestore may seem complicated and intimidating. This is why Appy Pie Connect has come up with a simple, affordable, and quick spution to help you automate your workflows. Click on the button below to begin.