Easy & pretty Sign-in control for HTML/JS with OAuth2.0, Google+API & CSS

by +Cesar Anton 

@reicek

When we make a Webapp or a site, we usually need to provide some kind of registration and sign-in, but it's hard and boring for the user to register and remember usernames and passwords for many sites, and some even avoids doing so (unless they really trust/need the site) to avoid sharing his password (most people use the same password for everything or most things).

Using Google+ API and OAuth 2.0 to do the sign-in is very convenient, but for you and your client's/users, since most people will be already signed into they're Google account, all they will need to do is to authorize your App the first time they use it and it will take just 2 or 3 clicks and no typing, and once done it will always remember it, or at least until the user asks to cancel the App authorization, but the best part is that you get the User's name and even the Google+ profile picture, this looks much better than the old-fashioned text-only username that most sites still uses.

The first thing you need to be able to use the Google+ API is to have or create a Google Project, if you don't have one, please follow these steps, if you have one, you need to activate the Google+ API service in the "Services" tag of the Google APIs console.

Note: The URL for your Services in the Google APIs console looks like the following:
https://code.google.com/apis/console/b/0/#project::services

For your code, you will need to manually check 3 things:

1. A client ID for web applications.  You need to create a new one for each web app that you create. To do this, go to the Google APIs console and click on "Create another client ID...".  In this case we are going to use "Application type" -> "Web application" and for the "Your site or hostname" since we are going to use Google Drive HTML hosting, your address would be something like: "https://googledrive.com/host/", the "Javascript origins" will be automatically set to "https://googledrive.com".
2. The API key. This can be found in the "Simple API access" section at the bottom of the Google APIs console.
3. The Scopes. This is where you specify the access rights required by your WebApp, in this case since we only need access to the users Name and profile picture, we are only going to need access to the users Google+ data. When the user is asked for authorization, he or she will see a list explaining the access rights your app is requesting. You can read more about scopes in this part of the documentation.
   
   To start coding, we need to put all this information inside variables, let's start by doing so:

We can find the rest of the Javascript code required to handle the authorization process on this part of the documentation, so let's make a copy:



So far we haven't moved away from the documentation example and it's usually a good idea to keep it unchanged to make debugging easier, the real action comes when making the API request, this is where we make the adjustments necessary to properly display the user's information with our CSS, for this example let's use a great Open Source Metro Style CSS from http://metroui.org.ua/, notice the adjustments when building the HTML code:



Now we need to add a Login button, to keep things neat, the Javascript in the authorization process hides the button when the user has a valid token (and therefore it's logged in), it is important to add this button on every page that we want the user's information (even if it's not a log-in page) this also helps to enhance security and is convenient for the user to log-in from any part of the UI in certain scenarios (E.g. when the user delete's the browser's cookies, and therefore the OAuth token). It is also interesting to point out that this also means the user is logged to a Google account on that browser, this means that you can retrieve the user's files using a Google AppsScript Script Servlet (as described on this article).

Let's look at the HTML needed for the log-in button of the example.



You can see it all working together in the following Google Drive hosted page (click here to read how to host your own).

Example

Feel free to let us know about your ideas, suggestions, requests, etc at the bottom of this article.

Happy coding!

How to parse ScriptDB JSON data into Spreadsheets

by +Cesar Anton 

@reicek

Using ScriptDB JSON data is very handy in many scenarios, specially when you need to avoid accidental overwriting when there are two or more instances trying to use the same data.

In "How to share/store Spreadsheeds as JSON data with ScriptDB!" I talked about how to populate your ScriptDB with a Spreadsheet parsed into flat JSON, now let's explore how to put that JSON data back into a Spreadsheet, and this can be done either manually for things like creating an Up-to-this-moment report, or programmatically, so you can keep only your most recent and fast-changing data in ScriptDB and keep a very well organized and readable backups inside Spreadsheets.

Let's take a look at our example Spreadsheet (it's the same one as in the previous article):



You can read an example about how to parse your ScriptDB JSON data into a new sheet in the documentation, so let's use it with a few adjustments so that every time you call the script, it dumps the data inside a sheet whose name is the date of such backup. This is very handy if you plan to use automated reports, doing it manually is very useful for things like "closing" a working shift of a Point of Sales, don't forget to empty the ScriptDB's contents in this scenarios after the data is safe inside the sheet.

Now its time to create a new script, let's get a look at our code:



Everytime we run our script, a new sheet (with the date as name) will be created. Our ScriptDB JSON data looks like this:

{"price":5,"name":"Razor blade","salesToday":0,"onStorage":50}
{"price":2.5,"name":"Toothbrush","salesToday":0,"onStorage":100}
{"price":3,"name":"Liquid Soap","salesToday":0,"onStorage":80}
{"price":3.7,"name":"Shampoo","salesToday":0,"onStorage":80}
{"price":2,"name":"Toothpaste","salesToday":0,"onStorage":150}

And once it's inside a new sheet, it will look like this:



Do note that the order of the rows and/or columns might be different from the original table, so plan your reports accordingly.

Feel free to let us know about your ideas, suggestions, requests, etc. at the bottom of this article.

Happy coding!

How to share/store Spreadsheeds as JSON data with ScriptDB!

by +Cesar Anton 

@reicek

There are many cases were we want to share data between many Apps, and one of the most popular ways it's to use Google Spreadsheets as a Central DB, and they do a great job on letting you share, download and access the Database in a very human way, but most of the time we want to efficiently query Data, but with Spreadsheets you need to know which cell has your data, filter using the visualization API or do some parsing... this makes it heavy and slow on large Data sets opposed to ScriptDB who is great for querying specific data!

Another limitation of using Spreadsheets is that they are not very good for handling shared accumulators, it's prone to over-writing when two or more users are working with the same cell at the same time, let's imagine the cell's current value its 3, and both client's add a +1, the cell's value its likely to end up as a 4 instead of 5... To prevent this ScriptDB has 2 different kinds of "locks", one public and one private, the difference is that public only allows 1 instance of the APP to run at any given time and the private lock only allows each USER to run 1 instance of the App at any given time.

Each script project get's a "sandboxed" ScriptDB for it's own use, meaning it's isolated from the rest of the users data and other ScriptDB instances from the same user. But were it really shines it's when you use a single ScriptDB for many Apps as a share point, you just need to create a tiny Apps Script webapp and share it as a library to get shared access to it's ScriptDB... not shiny enough? well, you can create many specific ScriptDBs to make everything safer, and organized by simply creating different library's and calling them as needed (creating different library's allows you to limit specific user's access) ... not enough? well, ScriptDB can be easily transferred to a Spreadsheet for a much more readable long term storage of our data. With some careful planning you can make smart data handling that's its fast, efficient and la piece de resistance... it's all free!

So, which are the limits of ScriptDB? According to the Documentation, they are actually  large enough for most cases, the quotas are per-user (so the limit is shared across all user's Apps, or in the case of the library, it goes against the owner of the library) and it depends on the kind of user:

> 50MB for consumer accounts.
> 100MB for Google Apps accounts
> 200MB for Google Apps for Business/Education/Government accounts.

Since it's all Text data wrapped inside JSON objects, this can be quite a lot of data, even for 50MB. So we are usually more limited to the query limits (default 200, maximum 50,000) and the time limit for an Apps Script instance (maximum 6 minutes processing time), in most cases it's a good idea to dump all the ScriptDB's contents inside an Apps Script sheet or sheets for more comfortable future reference and to avoid saturating the ScriptDB's query's responses.

Also keep in mind that ScriptDB it's specially useful with current and fast changing data, and Spreadsheets its better for long-term storage and for making automatic reports. In most scenarios the best approach it's to create a Sheet everyday with the Date as a name and dump the ScriptDB content on it... Also you can start a new Spreadsheet every month, all automatically and, if done properly, you will end up with a Database that your customers will love for being cheap, fast, flexible and the automatic reports are a great plus (you can even send a custom report via e-mail every time ScriptDB is dumped inside a Sheet).

Without more preamble, let's hit the code! We'll start by creating a Library that returns a ScriptDB instance that can be shared between different apps, create a new script and you only need this 3 simple lines of code!

To use this script as a library, remember you need to write down the script's project key, to get it, go to File - > Project properties.

If you plan to share access to this library with other users, remember to set the "Sharing Settings" which can be found in File - > Share...

To import your library, create (or open) a new Apps Script project and go to Resources - > Manage Libraries and write you Library's project key, inside the "Find a library" box.



Now, it's time to put some JSON data inside our library's ScriptDB! in most cases, we want to get our data from a Spreadsheet either because the Data is already on one, or simply because it is easier to write, as an example let's use the following spreadsheet:

The Apps Script code needed to do this, can be found on this part of the documentation, let's see a copy (with a few modifications):



This will make a flat JSON version of our Spreadsheet inside our shared ScriptDB! Flat JSON is very easy to work with, and remember you can put it back or make a copy in a Spreadsheet at any time.

There is a example code in the documentation to make a quick-check of your ScriptDB content, just create a new script with the following code:



Now you can see the ScriptDB's content by checking the Apps Script Log, our example spreadsheet data would look like this:

{"price":5,"name":"Razor blade","salesToday":0,"onStorage":50}
{"price":2.5,"name":"Toothbrush","salesToday":0,"onStorage":100}
{"price":3,"name":"Liquid Soap","salesToday":0,"onStorage":80}
{"price":3.7,"name":"Shampoo","salesToday":0,"onStorage":80}
{"price":2,"name":"Toothpaste","salesToday":0,"onStorage":150}

Feel free to let us know about your ideas, suggestions, requests, etc at the bottom of this article.


Happy coding!