Skip to main content

Get Support
Have a question? Let's get you an answer!

How Do I...? Troubleshooting Code & Webhooks Developer Zone

Other Help Resources
Wait, there's more! Check out these Community sourced spaces for additional resources.

Zapier Help Center Hire an Expert

Learning Resources
All of our best learning resources can be found here.

Featured Articles Show & Tell Zapier's products AI Hub Zapier Tools Hub

Share your knowledge
Give back to the Community by sharing your automation wins and helping to answer questions.

Share your success See unanswered questions User Groups

Other Learning Resources
Some of our favorite additional ways to upskill your Zapier knowledge.

Zapier Learn Zapier Blog Zapier Webinars

Product Updates
Check out what is new and upcoming with Zapier with our regular product updates.

Product Updates

Early Access Program
Want to join Early Access? Learn more here.

Best answer

How do I parse out Smartsheet row data from an API call for use as line items?

Phil Robbins
Forum|alt.badge.img+1

Hey there folks,

I’ve been trying to set up a Zap that takes Smartsheet row data and creates a multi-line Xero invoice. One of my efforts has yielded inconsistent behaviour detailed in this thread, so I have tried another method using a GET Sheet API call. This has pulled the sheet data fine, but I’m struggling to manipulate the row data into a form where I can use it as line items for the Create Invoice step in Xero.

I’ve managed to use a Formatter step to extract the row IDs from the sheet. My plan is to pass these through a loop and perform a Get Row API call to get the row data. Again, the data has pulled through, but the way Smartsheet provides its cell data consists of a Column ID, a Value and a Display value as follows:

This means that when I try to reference these values in a subsequent step, they are grouped by the data type name and are not available individually:

I’ve then tried another line item Formatter step which gives me the following:

But if I then try to reference the output of that step, they are still grouped as before:

This is my Zap so far:

Am I going about this the wrong way? Is there a way of unpicking this grouped data such that each cell value can be referenced downstream?

Many thanks in advance!

Best answer by Phil RobbinsBest answer by Phil Robbins

@Troy Tessalone having said that, using a combination of the code generator in Zapier, some refinement from ChatGPT, and a final bit of fettling from my side, I have a working solution which looks like this:

Code-wise, step 2 looks like this:

# Importing the requests library to make HTTP requests
import requests

# Defining the URL for the API call. Replace [Your Sheet ID] with the actual Sheet ID.
url = "https://api.smartsheet.com/2.0/sheets/[Your Sheet ID]"

# Defining the headers for the API call. Replace [Your Access Token] with the actual Access Token.
headers = {
    "Authorization": "Bearer [Your Access Token]",
    "Content-Type": "application/json",
}

# Making a GET request to the Smartsheet API to retrieve the data from the specified sheet
response = requests.request("GET", url, headers=headers)

# Converting the response data to JSON format
data = response.json()

# Initializing an empty dictionary to store the grouped data
grouped_data = {}

# Looping through each row in the sheet
for row in data["rows"]:
     # Checking if the row contains the specified value. If not, the row is skipped. Note the value 3 refers to the fourth column from the left in the sheet, which is where the Invoice Number is found
    if row["cells"][3]["value"] != input_data["invoiceNumber"]:
        continue
    # Looping through each cell in the row
    for cell in row["cells"]:
        # Checking if the 'value' key is present in the cell dictionary
        if "value" in cell:
            # Checking if the column ID of the cell is already a key in the grouped_data dictionary
            if cell["columnId"] in grouped_data:
                # If the column ID is already a key, append the cell value to the list of values for that key
                grouped_data[cell["columnId"]].append(cell["value"])
            else:
                # If the column ID is not a key, create a new key-value pair in the grouped_data dictionary
                # The key is the column ID and the value is a list containing the cell value
                grouped_data[cell["columnId"]] = [cell["value"]]

# Storing the grouped data in the output variable as a dictionary
output = {"grouped_data": grouped_data}

Thanks for the pointers!

View original
Did this topic help you find an answer to your question?

11 replies

Troy Tessalone
Forum|alt.badge.img+14

Hi @Phil Robbins 

Try passing the data from Step 2 to Step 4 (Looping), and remove Step 3.

Data from Step 2 is returned as an array of line items already, and the Looping action handles line items, so you shouldn’t need step 2.

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
Phil Robbins
Forum|alt.badge.img+1
  • Phil RobbinsBeginner
  • Author
  • Beginner
  • 19 replies
  • January 9, 2024

@Troy Tessalone thanks for your response.

While it does appear that step 3 was redundant, that section of the Zap was already working as intended, i.e. iterating through the row numbers to pull row data. Interestingly, all the data I actually need is already retrieved by the GET Sheet, but it is in one big block. If I point the looper at the Display Value each loop produces this:

You can see that everything on the top line up to 10.1 matches the output I was retrieving from my GET Row call in my original post.

I need to somehow ‘de-array’ either the sheet or row data so that each value has a unique identification that I can select from downstream. I use Bridge by Smartsheet a lot, which allows you to do this, but I’m unable to use that platform for this application.

I feel there must be a way of doing this in Zapier - the data is there and it’s just my knowledge of Zapier that’s in the way.

Troy Tessalone
Forum|alt.badge.img+14

@Phil Robbins 

You may have to use a Code step to make the API request to then handle the returned data payload JSON as you want.

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
Phil Robbins
Forum|alt.badge.img+1
  • Phil RobbinsBeginner
  • Author
  • Beginner
  • 19 replies
  • January 9, 2024

@Troy Tessalone 

Ah. Learning to code wasn’t on my list of New Year’s resolutions. But I guess this sort of thing is what ChatGPT was invented for, right?

Phil Robbins
Forum|alt.badge.img+1
  • Phil RobbinsBeginner
  • Author
  • Beginner
  • 19 replies
  • Answer
  • January 9, 2024

@Troy Tessalone having said that, using a combination of the code generator in Zapier, some refinement from ChatGPT, and a final bit of fettling from my side, I have a working solution which looks like this:

Code-wise, step 2 looks like this:

# Importing the requests library to make HTTP requests
import requests

# Defining the URL for the API call. Replace [Your Sheet ID] with the actual Sheet ID.
url = "https://api.smartsheet.com/2.0/sheets/[Your Sheet ID]"

# Defining the headers for the API call. Replace [Your Access Token] with the actual Access Token.
headers = {
    "Authorization": "Bearer [Your Access Token]",
    "Content-Type": "application/json",
}

# Making a GET request to the Smartsheet API to retrieve the data from the specified sheet
response = requests.request("GET", url, headers=headers)

# Converting the response data to JSON format
data = response.json()

# Initializing an empty dictionary to store the grouped data
grouped_data = {}

# Looping through each row in the sheet
for row in data["rows"]:
     # Checking if the row contains the specified value. If not, the row is skipped. Note the value 3 refers to the fourth column from the left in the sheet, which is where the Invoice Number is found
    if row["cells"][3]["value"] != input_data["invoiceNumber"]:
        continue
    # Looping through each cell in the row
    for cell in row["cells"]:
        # Checking if the 'value' key is present in the cell dictionary
        if "value" in cell:
            # Checking if the column ID of the cell is already a key in the grouped_data dictionary
            if cell["columnId"] in grouped_data:
                # If the column ID is already a key, append the cell value to the list of values for that key
                grouped_data[cell["columnId"]].append(cell["value"])
            else:
                # If the column ID is not a key, create a new key-value pair in the grouped_data dictionary
                # The key is the column ID and the value is a list containing the cell value
                grouped_data[cell["columnId"]] = [cell["value"]]

# Storing the grouped data in the output variable as a dictionary
output = {"grouped_data": grouped_data}

Thanks for the pointers!

SamB
1 person likes this
  • SamB
    SamB
SamB
Community Manager
Forum|alt.badge.img+11
  • SamBCommunity Manager
  • Community Manager
  • 7859 replies
  • January 11, 2024

That’s fantastic news @Phil Robbins! Thanks so much for following up here to share your solution, it’s much appreciated! 🤗

Looks like you’re all set for now but please do reach out in Community again if you run into any trouble. In the meantime, happy Zapping! ⚡ 

Phil Robbins
Forum|alt.badge.img+1
  • Phil RobbinsBeginner
  • Author
  • Beginner
  • 19 replies
  • January 11, 2024

Thanks @SamB. This approach turned out to be far more task-efficient than triggering off every row. I went from 4-5 tasks per line item to 2 tasks per invoice, which is a great result. ⚡⚡

SamB
Iliyan Slavov
2 people like this
  • SamB
    SamB
  • Iliyan Slavov
    Iliyan Slavov
T

Hi @Phil Robbins, I came across your original query and wanted to ask a quick question. 

I understand its been solved for your specific use case, but I was hoping you could share some detail on how your original Smartsheet API call worked to get the RowID.

I have a unique reference on every row within Smartsheet, so I was looking to make a GET query to gather the RowID and UniqueRef from every row within my sheet.
 

i.e. get Column2 value, as well as the Smartsheet internal RowID for every row within this sheet.

Phil Robbins
Forum|alt.badge.img+1
  • Phil RobbinsBeginner
  • Author
  • Beginner
  • 19 replies
  • April 15, 2024

@Thomas Halpin the sequence of operations I would use for something like this is:

  • GET sheet (based on sheet ID) from Smartsheet. This should give you an array of row objects that includes their IDs.
  • Loop through the array for as many times as there are objects and pull out the column value(s) you are looking for.

Below is an snip from Bridge by Smartsheet, which I use extensively (as I work for a Smartsheet partner). As you can see, the GET sheet method pulls back all the data you need (all the cell values, plus the row ID right at the bottom).

You should be able to build this out in Zapier without too much trouble. The issue I had was having to reformat how the data was presented, so I could do what I wanted with it. I’m not sure if this is something you’ll have to work through yourself, but let me know how you get on.

SamB
1 person likes this
  • SamB
    SamB
T

Thanks @Phil Robbins.

Turns out there is a function within the Smartsheet action list that you can use to “GET RowID”. 

So I am using that, determining the RowID, and then using that value for the associated update in the next step.

 

SamB
1 person likes this
  • SamB
    SamB
Phil Robbins
Forum|alt.badge.img+1
  • Phil RobbinsBeginner
  • Author
  • Beginner
  • 19 replies
  • April 17, 2024

@Thomas Halpin that looks like a useful function to be aware of. Thanks for sharing!

SamB
1 person likes this
  • SamB
    SamB

Reply


Terms & Conditions