The book takes readers first through a detailed Google Sheets tutorial, then dives into deeper features that help readers build their own custom tools and apps using Google Sheets’ most powerful features.
The Google Spreadsheets API allows you to send simple queries to retrieve subsets of the rows in your sheet – it’s as easy as appending the query to the end of the URL.
For example, if I have a column called “Quantity” and want rows with Quantity greater than 9, I would append ?sq=quantity>9 to the end of the URL and issue the GET request (don’t forget that column names are always converted to lowercase and spaces are removed).
The other method is to add the sq query parameter to the Guzzle $httpClient. I’ve illustrated this in my example code.
The Rackspace web dashboard allows you to attach a certificate and private key to a cloud load balancer for ssl offloading. But even though the load balancers support SNI and multiple certificates, neither the web console nor the “rack” command line scripts expose this functionality.
If you want to attach additional certificates to a load balancer from the command line, check out racklb.sh at https://github.com/karlkranich/racklb. The script doesn’t do a lot yet, but does the two things that I miss most from the web console:
Expose the private (ServiceNet) address of a load balancer
Manipulate the certificate mappings
To get started with it, clone the repo and check out the readme and “racklb.sh -h”
Thanks to Adam Bull at www.haxed.me.uk for the inspiration and sed magic in this script!
It turns out that my Google Sheets API instructions don’t work right away on Windows. People running WAMP or XAMPP have been reporting “cURL error 60: SSL certificate problem: unable to get local issuer certificate”.
For some reason, cURL on Windows doesn’t come with a certificate bundle, so it can’t verify Google’s certificate to establish a TLS/SSL connection. There are many articles out there making it sound really simple — just
download a cacert.pem file
add a line to your php.ini file: “curl.cainfo = path to cacert file“
But it took me hours to work out the details. First, the cacert.pem file that most people point you to (at curl.haxx.se/docs/caextract.html) didn’t work for me. After a while, I found that the cacert.pem from Shane Stebner at flwebsites.biz/posts/how-fix-curl-error-60-ssl-issue made all the difference. I haven’t figured out why.
Second, note that there are at least two php.info files on your system: one for running php from the command line (mine is C:\wamp\bin\php\php5.5.12\php.ini), and another for when php is called by Apache (for me, that’s C:\wamp\bin\apache\apache2.4.9\bin\php.ini). Since I’m running php from the command line, I only edited the first one.
I saved the cacert.pem file in c:\wamp and added this to my php.ini: curl.cainfo = c:\wamp\cacert.pem. It didn’t matter which kind of slashes I used.
Hope this helps a few people start accessing Google Sheets with PHP!
Here’s how to get up to speed with the Google Sheets cell-based feed and PHP.
My previous posts have illustrated the use of Google’s PHP API client to interact with Google Sheets using list-based feeds. This method is ideal for reading from and writing to spreadsheets that have a single header row, but isn’t useful for more complicated sheets.
The cell-based feed allows individual cells to be read and written. I’ve written some example PHP code that implements the cell-based feed methods to help get you up to speed a little quicker. Google documentation is here.
To start, follow the steps in this post to set up your service account, share a spreadsheet, and install the API client. Then you should be able to adapt my example code to interact with your spreadsheet.
Please let me know if you have any comments or questions!
Continuing my series on using PHP to manipulate Google Spreadsheets, I’ve added a section to my code that reads the XML representation of a spreadsheet and then parses out the rows and columns. Note that whenever you’re working with list-based feeds, we’re assuming that the first row contains column headings.
Using SimpleXML, it wasn’t difficult to parse out the cell data. The biggest hurdle was learning about XML namespaces — at first, I thought that the XML had tags like “gd:etag” or “gsx:quantity” and had to learn that gd and gsx were namespaces. In SimpleXML, several of the methods take a namespace as a parameter. You can specify the prefix or namespace URL as described in the doc for the children method here.
I think it’s also possible to get the spreadsheet data as json, but I haven’t tried that yet.
We have an increasing number of Windows 10 machines in our office, and realized that we couldn’t get them to connect to shares on our OS X 10.10.5 (Yosemite) server. We have a bunch of Local Network Users, and had no problem connecting as them from Windows 7 machines. But Windows 10 machines would just pop up the authentication dialog again with the message “The specified network password is not correct”.
I tried all of the Local Security Policy changes that people have mentioned on the ‘net, and nothing worked. I finally came across somebody mentioning that you have to put the server NetBIOS Name in ALL CAPS when you authenticate. So, in our case, we needed to make one Local Security Policy change on the Windows 10 clients and put the server name in all caps when authenticating.
Yesterday I updated my example PHP code for manipulating Google Spreadsheets with the Google-provided PHP API client. If you tried getting it to work recently and were frustrated, take another look. Google made major changes to the client that required changing my code.
Every once in a while I need to create a video DVD that will work in a standard DVD player. With Windows 10, Microsoft no longer provides Windows DVD Maker, so I needed to find a replacement.
DVD Flick does the job quickly and easily. It’s simple to add titles (individual videos), add regularly spaced chapters (so you can skip forward and back a few minutes at a time), and burn a DVD with a simple menu.