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.
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.
Comments
Post a Comment