API & Developers

API & Developers

Connect with other developers from around the world and collaborate on ideas using the Smartsheet API.

404 - Error Code 1006 "Not Found"

I am attempting to get an automation in place between Smartsheet and Excel. When running the script I am receiving a Response Status 404 with a response body error code 1006 message Not Found for every singe row. I have confirmed that nothing is deleted, permissions are fine and everything looks like it should. I just can seem to get over this error. Below is the script incase I am missing something here. (I have removed the API key.)

import openpyxl
import requests
import re

Smartsheet API setup

smartsheet_token = 'key holder

smartsheet_base_url = 'https://api.smartsheet.com/2.0/'

Smartsheet sheet link and column names

sheet_link = 'https://app.smartsheet.com/sheets/6074427292733316'
code_column_id = 5682219763060612 # Code Column ID
validation_verification_column_id = 3791181712674692 # Validation Verification Column ID

Excel file setup

excel_file_path = file path holder
sheet_name = '2024-11-20 3_29pm (12)'

Load the Excel workbook

wb = openpyxl.load_workbook(excel_file_path)
sheet = wb[sheet_name]

Function to extract sheet ID from the sheet link

def extract_sheet_id(sheet_link):
match = re.search(r'/sheets/(\d+)', sheet_link)
if match:
return match.group(1)
else:
raise ValueError("Invalid Smartsheet link. Unable to extract sheet ID.")

Function to get data from Smartsheet

def get_smartsheet_data(sheet_id):
url = f'{smartsheet_base_url}sheets/{sheet_id}'
headers = {
'Authorization': f'Bearer {smartsheet_token}',
'Content-Type': 'application/json'
}
response = requests.get(url, headers=headers)

# Debugging: Print the response status and body
print(f"Response Status: {response.status_code}")
print(f"Response Body: {response.text}")

if response.status_code != 200:
print(f"Error: Could not fetch data from Smartsheet. Please check your API token and sheet permissions.")
return None

return response.json()
Function to update the Validation Verification field in Smartsheet using cell data

def update_smartsheet_validation_verification(row_data, validation_data):
url = f'{smartsheet_base_url}rows/{row_data["rowId"]}'
headers = {
'Authorization': f'Bearer {smartsheet_token}',
'Content-Type': 'application/json'
}

data = {
'id': row_data['rowId'],
'cells': [
{
'column_id': validation_verification_column_id, # Hardcoded column ID for "Validation Verification"
'value': validation_data
}
]
}

# Debugging line: Print the request details for troubleshooting
print(f"API Request to update row ID {row_data['rowId']}: {data}")

response = requests.put(url, json=data, headers=headers)

# Print API response status and body for debugging
print(f"Response Status: {response.status_code}")
print(f"Response Body: {response.text}")

# Check the status code for success
if response.status_code == 200:
return True
else:
return False
Function to find all matching rows in Excel for a given code

def find_matching_rows_in_excel(code):
matching_rows = []
code_str = str(code).strip() # Convert to string and strip leading/trailing spaces
for row in sheet.iter_rows(min_row=2, max_row=sheet.max_row, min_col=1, max_col=1): # Check column A (index 0)
excel_value = str(row[0].value).strip() # Convert Excel value to string and strip
if excel_value == code_str:
matching_rows.append(row)
return matching_rows

Main logic to process data and update Smartsheet

def main():
# Extract sheet ID from the sheet link
sheet_id = extract_sheet_id(sheet_link)
print(f"Using Smartsheet sheet ID: {sheet_id}") # Debugging line to show the extracted sheet ID

# Get Smartsheet data
smartsheet_data = get_smartsheet_data(sheet_id)

# If no data is returned from Smartsheet, exit
if not smartsheet_data:
return

# Loop through rows in Smartsheet
for row in smartsheet_data['rows']:
print(f"Processing row ID: {row['id']}") # Debugging line to show the row being processed

code = None
row_data = None
# Loop through the cells to find the "Code" column and its data
for cell in row['cells']:
print(f"Cell data: {cell}") # Print the individual cell data for debugging
if cell.get('columnId') == code_column_id: # Check if 'columnId' exists
code = cell.get('value')
row_data = {'rowId': row['id'], 'cell': cell}
break

if code:
print(f"Found code: {code} in Smartsheet.") # Debugging line to show the code found

# Find matching rows in Excel
matching_rows = find_matching_rows_in_excel(code)
if matching_rows:
print(f"Found matching row(s) for code {code} in Excel.") # Debugging line to show matching rows
# If one or more matches are found in Excel, update the "Validation Verification" column with "Pass"
status = update_smartsheet_validation_verification(row_data, "Pass")
if status:
print(f"Successfully updated row ID {row_data['rowId']} in Smartsheet with 'Pass'.")
else:
print(f"Failed to update row ID {row_data['rowId']}.")
else:
print(f"No matching row found for code {code} in Excel")
else:
print(f"No code found for row {row['id']} in Smartsheet")

if name == "main":
main()

Answers

Trending in API & Developers