404 - Error Code 1006 "Not Found"
![swightman](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
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_token = 'key holder
smartsheet_base_url = 'https://api.smartsheet.com/2.0/'
Smartsheet sheet link and column namessheet_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_path = file path holder
sheet_name = '2024-11-20 3_29pm (12)'
wb = openpyxl.load_workbook(excel_file_path)
sheet = wb[sheet_name]
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.")
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 datadef 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 codedef 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
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
-
Hi @swightman!
Currently, Smartsheet Support does not handle SDK-related inquiries. If you’ve tested and found an issue with the SDK, please report it on the relevant GitHub page. You can access the links to these pages in our API documentation here: Smartsheet API Documentation.
Should you not get a response from the Community, consider checking out StackOverflow to connect with other developers to get more help.
Cheers,
Isaac.Need more information? 👀 |Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 |Global Discussions
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 489 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives