Has anyone mapped their Smartsheet files using the API?

Garrett Henke

2 part question here. My company is currently looking at ways to map how all of our sheets link to one another in a consolidated database. A thought that came to mind is using the Smartsheet API to access all of the underlying data (sheet id, name, column ids, cross sheet references, etc.) and store them in a database so you can quickly identify what sheets/processes will be impacted by changing a sheet.

Another item that we are looking into is the best way document smartsheet builds. We currently use LucidChart but that can be time consuming and requires updates as sheets are added/deleted. Plus it is stored somewhere else so having to navigate to LucidChart find the right process and make the update isn't very efficient.

Does anyone have ideas on how to tackle both of these projects?

BKing

This is c#. I think we found it with the api documentation and now use it in a big api wrapper we have written. We used AWS DynamoDB to store results as gives schema flexibility.

Assumes you already have accessed the sheet.

    // The API identifies columns by Id, but it's more convenient to refer to column names

    private readonly Dictionary<string, long> columnMap = new Dictionary<string, long>();

    public Boolean MapColumns()

    {

      Boolean mapOk = false;

      columnMap.Clear();

      if (sheet != null)

      {

        // Build column map for later reference

        foreach (Column column in sheet.Columns)

          columnMap.Add(column.Title, (long)column.Id);

        mapOk = true;

      }

      return mapOk;

    }

Regards

Brian

BKing

This is c#. I think we found it with the api documentation and now use it in a big api wrapper we have written. We used AWS DynamoDB to store results as gives schema flexibility.

Assumes you already have accessed the sheet.

    // The API identifies columns by Id, but it's more convenient to refer to column names

    private readonly Dictionary<string, long> columnMap = new Dictionary<string, long>();

    public Boolean MapColumns()

    {

      Boolean mapOk = false;

      columnMap.Clear();

      if (sheet != null)

      {

        // Build column map for later reference

        foreach (Column column in sheet.Columns)

          columnMap.Add(column.Title, (long)column.Id);

        mapOk = true;

      }

      return mapOk;

    }

Regards

Brian

Garrett Henke

Thanks @BKing!

My C# knowledge is limited and I understand the structure of what you posted. However, when it comes to actually writing C# my skill level drops significantly! I am currently writing the code in Python, and I am able to extract the IDs, Name, and Permalink for each sheet/report/dashboard. I am now working on writing code to iterate over my id list to pull the remaining data.

How do you like AWS DynamoDB?

BKing

The api is super consistent. If you have the sheet id then just keep moving down through the sheet entities - sounds like you are well on your way.

DynamoDB is good - kind of self manages. I wouldn't go for any reserved capacity on your tables - we found, for data that you go to now and again, on demand is very cheap. For peace of mind enable point-in-time recovery.

Be very careful of you key structure, take time to understand how to spread the data over the table. For you, if you were to use sheet id as a key, you would end up with all you data in one partition and not get the performance benefit. You create a bottleneck.

We have run document handover for over 75 construction projects with no stress whatsoever. Workflow and reporting now in Smartsheet, documents and processing on AWS.

Good luck.