Skip to main content

Google Sheets Web App

 Greetings!

Has been yet another long period since my last post, but hopefully the type of content makes up for it.  Today I'm writing about a need that I had to document my entire movie collection in Google Sheets, because some that were on my local network, some on DVD, some on my Playstation Video account and as well as my small collection in Google TV.  All in all there is currently over 500 movies, and growing.

So the spreadsheet is fine on its own, but something nice and "pretty" was needed to list, search and filter by the movie's attributes.  I first got an API key to be used with OMDB API.  This allows me to get a JSON of the movie's IMDB attributes with a search from the title.  I then added two functions in the "Apps Script" component of the sheet:


From here, I could call the function to search (from a cell) with:

=API(CONCAT("http://www.omdbapi.com/?apikey=*******&t=",ENCODEURL(A1)))

That would return the JSON (assuming A1 is the cell where the title of the movie is), and then I can use something like:

=JSONKey(K1,"Year")

To return the Year the movie was produced (assuming K1 is the cell where the JSON is).  If you want to sort/filter this sheet, it is best to copy/paste the values from the returned data of these functions, to both reduce the number of calls you make to the API, and I find it works better for Google Sheets as well.

From here, go to https://script.google.com and create a new project.  Within the "Code.gs" window, use the following code:


This assumes the following:

  • Column K has your full JSON data
  • Column I has the location of where the movie is in your library (DVD, Google, etc.), and
  • Column L has any external path that you may want to use in order to play the movie from the new application
The data runner will fetch the data from the spreadsheet and return a JSON, while the doGet will be triggered when the web page is viewed.  Create a new file (page.html)

In this new file, put this as part of a script element:


This is a sample, but effectively on load, it will use the dataRunner code, and create DIV elements to display the data.  Use your own CSS and creativity from here!

Hope to see some of your own projects.

J.

Labels:

Comments

Post a Comment

Popular posts from this blog

question2answer Wordpress Integration

 Today I want to journal my implementation of a WordPress site with the package of "question2answer".  It comes as self-promoted as being able to integrate with WordPress "out of the box".  I'm going to vent a small amount of frustration here, because the only integration going on is the simplicity of configuration with using the same database, along with the user authentication of WordPress.  Otherwise they run as two separate sites/themes. This will not do. So let's get to some context.  I have a new hobby project in mind which requires a open source stack-overflow clone.  Enter question2answer .  Now I don't want to come across as completely ungrateful, this package - while old, ticks all the boxes and looks like it was well maintained, but I need every  page to look the same to have a seamless integration.  So, let's go through this step by step. Forum Index Update This step probably  doesn't need to be done, but I just wanted to mak...
Post a Comment
Read more

Running NodeJS Serverless Locally

 So it's been a long time, but I thought this was a neat little trick so I thought I'd share it with the world - as little followers as I have.  In my spare time I've been writing up a new hobby project in Serverless , and while I do maintain a staging and production environment in AWS, it means I need to do a deployment every time I want to test all of the API's I've drafted for it. Not wanting to disturb the yaml configuration for running it locally, I've come up with a simple outline of a server which continues to use the same configuration.  Take the express driven server I first define here: And then put a index.js  in your routes folder to contain this code: Voila! This will take the request from your localhost and interpret the path against your serverless.yml and run the configured function.  Hope this helps someone!
Post a Comment
Read more

Machine Learning: Teaching Wisdom of the Crowd

I got lost in an absolute myriad of thoughts the other day, and it essentially wound up wondering if we can teach machines to count, beyond of what it can see in an image, and I've come up with a small experiment that I would absolutely love to collaborate on if anyone (@ Google ?) else is interested. The idea is based on  the concept of the experiments performed using " Wisdom of the Crowd ", commonly in this experiment to use a jar of jelly beans and asking many people to make a guess as to how many is in there.  Machine learning can be used to make predictions from patterns, but it would have nothing to gain looking at one picture of a jelly bean jar to the next and being able to correctly identify that is in fact - a jar of jelly beans. But suppose we feed it several images of jars of jelly beans, along with all of the guesses people have made of how many is in there.  Can we then presume that feeding it a new image, it would be able to give us a fairly accurate c...
Post a Comment
Read more