Python Backup Script

I've recently had a lot of questions about how to back up your SmartSuite data. There are a number of approaches that can be taken, many of them involving third-party services like Make/Zapier. For those of you who are comfortable writing a little bit of code, however, you can consider taking an approach that uses the SmartSuite API directly.

I put together this Python script that retrieves all of the current (non-deleted) Solutions from a target workspace. Then we use that list of Solutions to retrieve Tables and their records, saving all of the data to CSV files in a formatted directory structure.

Please note that this code is provided only as a guide, and you should consider your particular needs, architecture and specific backup requirements before you use this in a live solution. Please let me know if you have thoughts, questions or additional requests that we can work through together. Happy coding!

'''
###############################################################################
# IMPORTANT:                                                                  #
#                                                                             #
# This script utilizes external dependencies that are not included in the     #
# standard Python library. To ensure proper execution, please install the     #
# required packages by running the following command in your terminal:        #
#                                                                             #
#     pip install pandas requests                                             #
#                                                                             #
# Note: The script is provided without warranty by SmartSuite. Please use it  #
# at your own discretion and review the code to ensure it meets your needs.   #
# You will also need to replace the WORKSPACE_ID and API_KEY values with your #
# own before running the script.                                              #
#                                                                             #
###############################################################################
'''

import io, os, re, requests, json
import pandas as pd
from datetime import datetime

today = datetime.today().strftime("%Y-%m-%d")

workspace_id = "WORKSPACE_ID"
api_key = "API_KEY"

# Request headers
headers = {
    "Content-Type": "application/json",
    "account-id": workspace_id,
    "Authorization": f"Token {api_key}"
}

# Get all solutions
response = requests.get("https://app.smartsuite.com/api/v1/solutions/", headers=headers)
response.raise_for_status()  # Raise an exception for error responses

solution_data = {solution["id"]: solution["name"] for solution in response.json()}

# Iterate through the solution IDs and call the applications API for each
for solution_id, solution_name in solution_data.items():
    print(f"- Solution: {solution_name} ({solution_id})")
    applications_response = requests.get(
        f"https://app.smartsuite.com/api/v1/applications/?solution={solution_id}",
        headers=headers
    )
    applications_response.raise_for_status()  # Handle potential errors here

    # Do something with the applications data
    applications = applications_response.json()
    print(f"Applications for solution ID {solution_id}:")

    for application in applications:

        print(f"- {application['name']}")
        fields = []

        for field in application['structure']:
            fields.append(field['slug'])

        field_list = {"visible_fields": fields}

        try:
            records_response = requests.post(
                f"https://app.smartsuite.com/api/v1/applications/{application['id']}/records/generate_csv/",
                data=json.dumps(field_list),
                headers=headers
            )
            records_response.raise_for_status()  # Raise an exception for actual errors

            content_type = records_response.headers.get("Content-Type")
            if content_type and content_type.lower().startswith("text/csv"):

                # Read CSV data with proper encoding
                # utf-8-sig is used to handle Unicode encoding properly and remove the Byte Order Mark (BOM), which can sometimes cause issues.
                df = pd.read_csv(io.StringIO(records_response.content.decode('utf-8')), encoding='utf-8-sig')

                # Create the directory with date
                os.makedirs(f"backup_{today}", exist_ok=True)
                solution_folder = solution_name.replace(' ', '_')
                os.makedirs(f"backup_{today}/{solution_folder}", exist_ok=True)

                # Sanitize application name for filename
                application_name_sanitized = re.sub(r"[^\w]", "_", application["name"])

                # Write CSV data with specified encoding
                df.to_csv(f"backup_{today}/{solution_folder}/{application_name_sanitized}.csv", index=False, encoding='utf-8-sig')
                print(f"CSV data saved to backup_{today}/{solution_folder}/{application_name_sanitized}.csv")
            else:
                print("Response is not CSV data.")

        except requests.exceptions.RequestException as e:
            print(f"Error processing application {application['name']}: {e}")
            # Continue to the next application