jdc-cunningham/google-spreadsheet-interface-with-ups-php-api-by-google-apps-script

Just a basic implementation to error check values and then do a call to an external API that hits UPS and updates the spreadsheet with the rate for the package.

About

This is a demo of interfacing Google Spreadsheet with UPS API (PHP) via a Google Apps Script (GAS). This should be the same output as the UPS rate calculator.

Example rate between two zip codes and a weight (WA to CA)

Example error checking per row

How it works

On the spreadsheet you can insert a script that runs on the spreadsheet using Google Apps Script. While your spreadsheet is open you can enter the Apps Script editor by going to Tools > Script Editor. Then in there, there are basic "hooks" you can use eg. onEdit which fire when a cell is modified(move cursor off active cell). In my case I had to change to a custom atEdit in order to do the external API request from inside Google Apps Script.

Side note: you'll probably want to have your GAS logs open so you can see the events in real time whenever you call Logger.log('your msg');. Those logs are viewed by going to View > Logs then click the link in the popup that takes you to Apps Script Dashboard. This is not the same thing as plain console.log('...'); in JS since this is running on the cloud.

The ideal way to make a Google Apps Script "professionally" would be to do it after getting oAuth permission and requesting access. Then you can insert the script I believe into the user's drive account/folder. Not sure how you associate it with a particular thing eg. spreadsheet at this time.

Anyway this was new to me so it was cool figuring it out.

Running this code

If you actually wanted to try and use this code as is, you'd need:

• your own domain, presumably with https and PHP running(note some required XML-related module(s))
  • move the contents of the ups-api folder to that domain
  • take out the .example from the .env.example file and fill in the fields(you need a UPS acct and API access)
  • note the VALID_API_KEYS this can be any string you want just to limit your GS script and endpoint from being used by anyone
• open up a Google Spreadsheet and from this spreadsheet get to the Google Apps Script editor
  • paste in the contents of google-apps-script.gs.js then update the endpoint url to match yours
  • note that the spreadsheet should match what you see in the gifs above due to the trigger col/row locations

At this point you should have everything you need. To do some intial checks by checking your PHP logs if requests from the GS are going through. Also check by doing Logger.log('...'); calls on the GAS side to see what's happening.

Basic error handling

The error handling checks if the minimum fields to make a UPS API call are met, particularly the zip from, zip to and weight field. The dimensions cell is technically not blank, the minimum that I found is 1 cubic inch(can't be empty). If a required cell is empty(from left to right), the alert prompt is triggered and the error cell is highlighted in red.

There is an error you can see in the second gif where the dimensions should be excluded from the trigger to validate since it's optional.

Google Apps Script

Apparently it is possible to develop locally through an npm package but I painstakingly developed in their editor and they have this forced parenthesis/indent matching like Ruby that was kind of annoying.

UPS API

The UPS API aspect is pretty much stock PHP from their API docs. You need your own account to use their service but the API is free surprisingly. You'll also need to enable some PHP modules eg. XML related. Other than that it's mostly making sure the fields are available, it took me a bit to figure out some missing fields in order to add the dimensions which I later found out weren't required.

There is some setup on the PHP side, namely enabling some XML php modules I think you'd call them.

Regarding files

The wsdl folder should be stock as it comes from their API examples. The index.php file is modified from their example as well, pretty much just added the dimensions and environment variable parsing.

Closing

Anyway I put about 16hrs into this between figuring out how to use the UPS API and then figuring out the GAS part as I had never used GAS before. I'm also aware Zappier does this to some extent.

Sidenote

I developed this a different way initially with my own interface. Sadly this little app won't really see the light of day anyway but it's not much better than the public quote calculator anyway. I don't know I kind of liked the design.