Smartsheet PHP API Version .01

Published by John on April 27, 2016 Under PHP

Recently, one of my business clients switched to using Smartsheet for managing their business leads.

If you haven’t used or heard of it, Smartsheet provides a web app that is similar to Microsoft’s Excel. It looks much like a spreadsheet, although has other features as well to make collaborating a bit easier, like reminders, comments, sharing, etc.

The client wanted all of their website leads, which were at the time just a series of contact forms built using the WordPress Contact Form 7 plugin, to be inserted into their SmartSheet. We initially looked into just using Smartsheet’s web-forms, which are contact forms designed for just this purpose. However, they lacked a lot of the configurability we needed, mostly in regards to styling.

So, we looked to using Smartsheets API to insert rows into the sheet. They do have several SDK’s linked to from their documentation page, including one for PHP. However the PHP one wasn’t really complete and only had a few built-in features.

Description

The below provides a php library for adding rows to a smartsheet, as well as attaching files to the row.

Disclaimer

This is a really early version, so will likely change. However, it seems to work well for what I am using it for…namely inserting submitted data from Contact Form 7 into Smartsheets. Prior to using this in any sort of live environment, you should test/review it yourself.

The response section, which uses an associative array, could probably be improved upon…

Prerequisites

To use this library, you need three pieces of information: Smartsheet API Key, Sheet ID, the Names of the Columns in your spreadsheet.

Smartsheet API Key: Your SmartSheet api key is like a password and should be kept secure. Do not post it publicly on the internet or elsewhere.

To get/create a key, login to Smartsheet and goto: Account -> Apps and Integrations -> Generate New Access Token

Follow the instructions on the screen and make sure to save the provided API key for later.

Sheet ID: Your sheet ID is a numeric identifier unique to each Smartsheet. For the purpose of this example, you will need to know the ID of the sheet you want to work with, although in later versions, I may retrieve this by the name of the sheet or something.

To find your sheet id, login to Smartsheet and goto: Select Sheet -> Right Click on the desired sheet tab and select ‘Properties.’ You will see your ‘Sheet ID’ here

Column Names: Smart sheet also uses a unique numeric ID for each column. Since these are not easily viewable from their website, you can use the column names when adding a row using the API.

Important: For the purpose of this test, the columns will need to have unique names. Failing to do this could result in in-consistent data when adding a row.

Download Version .01 Smartsheet Connect API

You can download the sample file here: Smartsheet Connect PHP API V.01

Example Usage: Adding a Row

The below is an example of how you could use the api to add a row to your worksheet:

require_once('libs/smartsheet_api.php');

$test = new smartsheet_connect('YOUR_API_KEY', 'YOUR_SHEET_ID', true);

$row_data = array('Name' => 'Test', 'Phone Number'=>"919-555-1212", "Email"=>'test@test.com', 'Company'=>'Test Company', 'Message'=>'Test Message', 'Country'=>'United States');

$row_id = $test->insert_row($row_data);

if($row_id !== false){
    echo "Row Added: {$row_id}";
} else{
    echo "An Error Occurred!";
}

In the above example, a row is added to the sheet, using the $row_data array. Note that the keys in the array must match the titles of the columns in your spreadsheet and you should not have any duplicate columns in the spreadsheet.

Make sure to replace ‘YOUR_API_KEY’ and ‘YOUR_SHEET_ID’ with the correct info for your account.

Example Usage: Adding a Row and An Attachment

require_once('libs/smartsheet_api.php');

$test = new smartsheet_connect('YOUR_API_KEY', 'YOUR_SHEET_ID', true);

$row_data = array('Name' => 'Test', 'Phone Number'=>"919-555-1212", "Email"=>'test@test.com', 'Company'=>'Test Company', 'Message'=>'Test Message', 'Country'=>'United States');
$file_path = "/tmp/test.txt";

$row_id = $test->insert_row($row_data);

if($row_id !== false){
    $result = $test->attach_file($row_id, $file_path);
    echo "Row Added: {$row_id}";
} else{
    echo "An Error Occurred!";
}

The above code adds a file that is located in the ‘/tmp/’ directory. The $file_path variable is the path to the file(which must exist) on the server/computer you are running the script from.

Note that there are two config variables here in the class, which you may need to adjust and while playing with it, debug is probably useful here:

$check_upload_path: When true, the class will check the path of the submitted file against the below variable, and attempt to make sure the file exists in that directory. This is to prevent someone from uploading files from /etc/ or something.

$base_upload_path: The path to look for uploaded files. Default is /tmp

Debugging

Both of the above examples have the debug variable set to ‘true’:

$test = new smartsheet_connect('YOUR_API_KEY', 'YOUR_SHEET_ID', true);

When debug is set to true several error messages may print as html blocks, such as if the api key is invalid or something else fails. When available the error_code and error message from smartsheet’s api will also print.

Integrating Smartsheets with Contact Form 7

For my client, we needed to take submissions from a contact form and put them into Smartsheets. I did so using the above library, along with Contact Form 7’s wpcf7_before_send_mail action hook.

If there is interest or I have some more time, I may include this as well, as it is a very straightforward php function.

Troubleshooting and Support

Again, this should not be used in production without using care and properly testing. I intended to rewrite portions of this, but needed something to get off the ground. If you have issues, enable debugging and see if a message is printed and check your PHP error logs. You can also post a comment here or use my contact form to request help.

8 Comments |