Our most common customers are probably like you. We are proud to serve solo consultants, small shops, medium businesses, and individual units of multi-national enterprises. In short, we are the best way to convert JSON to or from Google Sheets.
If you have a non-technical hero who manages datasets or performs analysis in spreadsheets, we are the essential glue that empowers that individual to be involved with the technical side.
We have written enterprise-grade code to transform technical JSON into Google Sheets, and visa versa.
We view the spreadsheet as a simple database. The first row is the "header" row that defines column names. If absent, SheetsDB uses the 1-based column number as a header. Then each row is a "record" which may or may not have values assigned within each column.
The first column of each row is the "id" field (this is also recommended by Google Sheets) that should be unique and set for every row. The rest of the spreadsheet is up to you.
All datatypes are strings in both directions. The reason for this is the Google Sheets implementation coerces datatypes as needed, and it is more stable to pass strings back and forth. It also forces your technical partners to treat the data as human input, which we think is a Good Thing™.
JSON is a plain-text data format used in many applications across industries and regions. You are most likely to see it on the web as part of REST endpoints. It is the most popular such format for new projects today.
Yes! SheetsDB will export the calculated results of formulas for all plans. However, to import formulas you will need to contact us for enterprise support.
We require access to your Google Profile and your Google Sheets via Google APIs.
Google Profile API: We use your Google Profile (what Google calls "basic access to information") in order to protect against abuse and limit free trials. Google only provides us a unique identifier for your Google Account. We do not request nor receive any personally identifiable information (i.e. we do not see your email address, name, or organization details). If Google inadvertently sends us extra or unrequested information, we discard it immediately.
Google Sheets API: We use the Google Sheets API to read and write data from within existing spreadsheets. SheetsDB does not have access to your Google Drive and cannot create new spreadsheets itself. This is a feature for most customers!
Auditing Included: One of the most important aspects of using the Sheets API is that all actions are recorded: If you use Google Apps your admin may audit all files SheetsDB reads and writes to ensure compliance. Obviously SheetsDB only reads from spreadsheets you select, but some organizations need to go a step further and prove SheetsDB hasn't accessed other files. Our use of the audit log enables us to say so credibly.
Enterprise Security Practices: We're proud to say SheetsDB follows best practices and should work well in organizations with compliance goals. Having worked in organizations subject to compliance mandates, we know it is more than the basic of using SSL everywhere. SheetsDB requests minimal permissions for the shortest duration (it requests an "online" OAuth2 token with a timeout of 1 hour that cannot be refreshed without user action and revoked at any time), and SheetsDB works hard not to retain or leak any information that flows through the service.
First, it is important to stress that SheetsDB only performs operations that you request. You may revoke access at any time, and if you have access to the Google Apps audit log you may confirm that SheetsDB behaves as advertised.
We do not store any spreadsheet listings or data, whether for export or import, to disk in the retail products. All user data is stored in the heap, and we have prevented any swap files from being written. This is the primary reason for the filesize limits for non-enterprise customers. We do not retain any data from the Sheets API longer than is necessary to complete the requested operations. We intentionally do not log customer data in any way because many of our enterprise customers have strict data security rules.
The Google Sheets API exposes something called the "Feed URL". Each Feed URL is a unique and opaque identifier for the individual spreadsheets in your account. We use the URL, encoded in base-64, in the SheetsDB API in order to select on which spreadsheet to perform your requested operations. Your browser and the SheetsDB API server record the base-64-encoded Feed URL in your history and the SheetsDB access logs, respectively. Google considers these URLs to be public, and we are not currently aware of risks posed from inadvertent disclosure. Nonetheless, we would be remiss not to mention them in our thorough threat model. We retain our logs only as long as necessary to confirm we are not victims of abuse.
We store the unique user identifier ("user_id") that the Google Profile API provides as a result of successfully authenticating with OAuth2. We use this unique identifier to limit abuse and enforce service plans. Otherwise, we do not store any other information from the Google Profile API. We do not request nor have access to your email, name, or organization.
If you purchase a pack of conversions, we use Stripe to handle and store all data related to the transaction. SheedsDB only retains a receipt identifier from Stripe and associates it with your unique user identifier. All other data that you provide during checkout is stored with Stripe.
We deeply respect your privacy, and you may at any time request that we "forget" or delete you. Email us at "hello" and we will comply within 14 business days.
As a best practice and to limit the immense complications of Google Drive, SheetsDB can only read and write data from existing spreadsheets in your account. If you have JSON you would like to import into a new spreadsheet, we recommend going to Google Drive in a separate browser tab and creating the spreadsheet. Afterwards, return to SheetsDB to import your data into the spreadsheet.
The Google Login OAuth2 permission prompt will suggest that SheetsDB can create new spreadsheets, however, this is not accurate after Google Drive was released. It is not possible for SheetsDB to create or delete spreadsheets. See this note in the Sheets API documentation for more information.
The short answer is nested types are complicated, and we think 80% of customers can manage by de-normalizing their data. If you have serious dataset needs (i.e. "Big Data") or the like, then SheetsDB may be the wrong tool for you.
The ideal for SheetsDB are datasets that humans can rationalize over, and we think that excludes nested types or foreign keys. There are always exceptions, and for those we require working closely together.
While we are sorry to see you go, we also are big fans and advocates of internet privacy. To that end, after the OAuth2 token expires (typically 1 hour) SheetsDB no longer has access to any part of your account or spreadsheets. You may go a step further and "revoke" the SheetsDB login permission in the future. See this Google help article for more information.
The impact of revoking access is that next time you try to log in to SheetsDB you will be prompted as if it were your first time. The revocation isn't permanent. The only reason to perform this action is if you want to clean up the list of applications or revoke before the 1 hour elapses.
Short answer: JSON is better for the technical people to use and non-technical people won't notice the difference.
In general there are many differences between JSON and CSV. However, the flat-schema JSON that most of our customers use is not really different than CSV except for one: JSON can be interpreted by browsers natively, CSV cannot. This means that JSON will "just work" with less code for a lot of popular projects today, which means getting to market faster and cheaper code to build and maintain. JSON also tends to be easier to work with in non-browser environments, making it a top choice for many technical projects.
So, if the intent is to make the data available as part of a technical project ‐ configuration, an item catalog, or a simplified CRM ‐ then SheetsDB JSON tends to offer better productivity, portability, and overall lower cost.
To be polite, most people who think CSV is "easy" to work with haven't worked with real-world examples. We recommend this light reading or this discussion to help explain. We are aware of Papa Parse, but we think providing JSON directly instead of requiring most customers to use one specific library is a better tradeoff.
If nested schemas are important to you, we do offer that feature with an enterprise contract.
We built this site using experimental and next-generation ES6 technology. We wrote the underlying service that handles the JSON < ‐ > Google Docs conversions in Scala, using Akka.