Insert and Delete Cells Google Sheets Add-on is shut down

If you’re still using my “Insert and Delete Cells” Add-on for Google Sheets, you’re now seeing: “401. That’s an error”, “Error: deleted_client”, and “The OAuth client was deleted”.

I shut down the “Insert and Delete Cells” Google Sheets Add-on yesterday because the functionality has been built into Google Sheets for a few years now.

You will find the Delete functions in the Edit menu and the Insert functions in the Insert menu.

Sorry for the abrupt shut-down of the Add-on.  I was trying to figure out a graceful way to remove the Add-on from the store, and instead made an irrevocable shut down choice.

I’m glad the Add-on was useful to people while it lasted!

Solved: Mac option key not working on Nixeus MODA Pro keyboard

There are plenty of articles and Youtube videos showing how to switch the Option and Command keys when you adapt a PC keyboard for use on the Mac.  (System Prefs – Keyboard – Modifier Keys – Choose the correct keyboard, then switch Command and Option).

But even after doing that, the Options key wouldn’t work.  I finally noticed the “Win Lk” light at the top right.  Gaming keyboards let you turn off the Windows key in case you accidentally hit it during a game, and that’s the key that is now mapped to Option for my Mac.

Turning if off just requires holding Fn and pressing F8, which also has a “no Windows” symbol.

iPhone tip: disable the extra click needed to unlock your phone

iphone-touchid-pexels-400In a recent iOS update, Apple started requiring an extra click of the home button to unlock your iPhone, instead of just allowing a “click with finger resting on Touch ID” to unlock the phone.  It’s not a big change, but just different enough to be frustrating.

To go back to the original behavior, go to Settings – General – Accessibility – Home Button and enable “Rest Finger to Open”.

How to move a Windows EC2 instance to another AWS account

ec2-iconHere are the steps that I’ve used to move some Windows Server EC2 instances to a different AWS account.  This method should work for any OS, but I’ve only tested with Server 2008 R2.

The simple version:

  1. In the source account, stop the instance
  2. Make a snapshot of the instance’s system volume
  3. Share the snapshot with the destination account number
  4. In the destination account, make a volume from the snapshot
  5. Create an instance with the same OS as the source instance
  6. Detach and delete the new instance’s system volume
  7. Attach the volume from step 4
  8. Start the instance

The detailed version:

Continue reading

Ultimate Guide to Google Sheets

zapier-sheets-guideZapier has published a free ebook called The Ultimate Guide to Google Sheets, and it mentions my “Insert and Delete Cells” Sheets Add-on!

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.

Here are links to the book:

– Zapier Learning Center: https://zapier.com/learn/google-sheets/
– Kindle store: https://www.amazon.com/Ultimate-Guide-Google-Sheets-spreadsheet-ebook/dp/B01IMV1NH2/
– iBooks store: https://itunes.apple.com/us/book/ultimate-guide-to-google-sheets/id1135333631

Sheets API access with PHP part 5: Querying rows

query-rowsThe 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.

See this page for more info.

SNI with Rackspace Load Balancers: racklb.sh script

CLBThe 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:

  1. Expose the private (ServiceNet) address of a load balancer
  2. 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!

Solved: cURL error 60 with Sheets API access from Windows

Broken_Lock_CurlIt 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

  1. download a cacert.pem file
  2. 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!

Sheets API access with PHP part 4: Editing Cells

cellfeedHere’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!

Parsing Google Sheets data with PHP’s SimpleXML

SimpleXMLAKA: Sheets API access with PHP part 3

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.