Skip to main content

Liberating Light Data

On to a new topic today, we've all heard the buzz about "Big Data" and how we can tackle querying in lightning fast speeds, but what are some of the best ways of working with highly mutable but tiny data sets? I'll lead you in by example on this one - I had recently had a custom developed PIM (Product Information Management) system, but also part of this system, I also kept a database of our stores and their respective catalogue versions.

For a while, it was good - until it wasn't. It didn't scale that effectively in the long term. The PIM should have only been just that, not a hybrid of extraneous bits and pieces of data. So I'm going to fast forward to the solution its now been moved to - I've created a workbook in Google Sheets and tied the integrations to this. This not only scales incredibly well, but you also have the luxury of cell-by-cell history out of the box.

So, how to proceed? First you need to add the component using Composer:
Keep in mind that this package self-requires other packages which it will download and update your packages.  A little annoying as even these components by themselves seems to be too big for a PHP Lambda Function...

Next, you will need to create a service account in the Google Cloud Platform.  There is a well detailed guide which can be found here.  Keep the JSON file it creates and the email address safe.

From there, you can start to prepare the PHP wrapper class to interact with your sheet.
This is our construct method, and prepares you to be able to start using the API's off the _sheets object.  To then access a particular Google Sheet document, you need it's spreadsheet ID.  Open one in your browser and inspect the URL:

https://docs.google.com/spreadsheets/d/<LONG TOKEN HERE>/edit#gid=0

The token is what you will be using.  I find it useful to create a private array variable of my sheet "collections" for easy naming reference:

Now you can try and create a simple get method for easy access to your data:
That almost covers it - now you'll just need to share the spreadsheet to the email of the service account, and you're all set.  I've use query string parameters here, you'll be able to call it like this:

http://your-server.com/endpoint.php?sheet=sheet1&range=A1:G5

You should then get a JSON object of the data contained in these rows.  Hope everything goes well for you and I've saved you a few hours in research!

Cheers,
Jess. 
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

Getting all deltas from Auth0

Before I get in to the solution of this article, let me tell you how it started and fill you in on the problem that arose.  I wrote a procedure to get daily deltas of users - those of which who had created/updated their account on the given day (and including the day before for good measure on the GMT timestamp).  The simple search criteria was just the following: updated_at:[yyyy-mm-dd TO yyyy-mm-dd] Simple, right?  the []'s being the dates are inclusive, while using {} would mean exclusively.  Auth0 lets you mix these on either side depending on your use.  While this is all well and good, Auth0 will limit the number of results (even with paging) to 1000 only. So, your first option is that you could have your procedure create a user export job, and then parsing through the results and eliminating those which do not meet your updated_at search criteria.  I can tell you first hand that eventually the amount of users will just get to be too much and cumb...
Post a Comment
Read more