Using data shuttle for weekly backups from smartsheet to sharepoint

nadimsobhani

I have successfully turned on the weekly export from smartsheet to sharepoint, but the action is "replace". I don't want to replace my file each week, I want a series of dated weekly backups for an audit trail. Is this possible?

Naveen14

Hi
There are two ways
1. Using Power Automate (recommended)
2. Using Smartsheet (explained below)

1. Create a Smartsheet Workflow for Weekly Exports:
   • Set up an automation workflow in Smartsheet to send a copy of your sheet to SharePoint every week.
     • Navigate to Automation → Create a Workflow.
     • Set the trigger as Weekly and choose the day and time for the export.
     • Set the action as Export Sheet to a SharePoint location.
2. Manual Naming Convention in SharePoint:
   • By default, Smartsheet overwrites the file with the same name in SharePoint. To avoid this, you’ll need to manually or automatically rename the file in SharePoint using a date-based naming convention.
     • For example, after each export, manually rename the file to include the date, such as SmartsheetBackup_20241015.xlsx (YYYYMMDD).

Workaround Using Smartsheet Attachment or Copy Sheet Automation

If you want to handle backups entirely within Smartsheet (without relying on SharePoint), you can use Smartsheet’s attachment or copy sheet automation:

1. Create a Workflow to Attach a Backup of the Sheet:
   • In Smartsheet, set up a workflow to send a copy of the sheet as an attachment to yourself or another file repository (e.g., OneDrive, Google Drive).
   • Navigate to Automation → Create a Workflow.
   • Set the trigger as Weekly.
   • In the action, choose Send as Attachment and select the format (Excel or PDF).
   • Configure the workflow to email this to your chosen recipient (yourself or a backup email address).
   • When you receive the email with the file, save it manually with a date in the filename.
2. Use "Copy Sheet" Action for Weekly Snapshots:
   • If you want to maintain historical versions of the sheet within Smartsheet, you can set up a workflow to copy the entire sheet each week:
     • Go to Automation → Create a Workflow.
     • Set the trigger to Weekly.
     • Choose the action as Copy Sheet.
     • Smartsheet will automatically create a new copy of the sheet with the name appended by "Copy of SheetName" each time the workflow runs.
     • After each copy is created, you can manually rename the new sheets by adding the date to the name (e.g., Backup_Sheet_20241015).

Summary of Native Smartsheet Solutions:

1. Weekly Export with Manual Renaming:
   • Automate the export to SharePoint and manually rename the file with a date.
2. Send as Attachment:
   • Automate sending a weekly copy of the sheet to an email or cloud storage, where you can store a dated backup.
3. Copy Sheet for Snapshots:
   • Automate weekly sheet copies and manually rename them to maintain a series of backups.

While Smartsheet doesn't offer automated date-stamped file names directly, these workflows should provide an effective workaround.

Neil Egsgard

@nadimsobhani , a solution we use is to:

1. Download the sheet on a schedule to a SharePoint location
2. Run a python script using task scheduler to rename the file.

We use a server synced to SharePoint using OneDrive and to run a BAT file that triggers the python script. The code below will need some tweaking.

BAT file

This file is triggered by Windows task schedule

@echo off

python path\to\your\script.py

Python file

This file contains the python code to rename the file.

import os

from datetime import datetime




# Specify the file you want to rename

original_file = 'example.txt'



# Get today's date in the desired format

today_date = datetime.now().strftime('%b %d %Y')



# Create the new file name

new_file = f"{os.path.splitext(original_file)[0]}_{today_date}{os.path.splitext(original_file)[1]}"



# Rename the file

os.rename(original_file, new_file)


print(f"File renamed to: {new_file}")

Neil Egsgard
Business Solutions Architect
Southern Alberta Institute of Technology