Smartsheet form + website data connection

Tania M · 09/18/24

Hello,

I'm exploring the possibility of connecting a Smartsheet form to our website's content management system. Specifically, I'm interested in implementing the following workflow:

A user accesses a Smartsheet form to propose updates to specific website content (e.g., a location description).
The user enters the new description and provides a comment explaining the change.
This submission triggers an approval process within Smartsheet.
Once approved, the updated content is automatically pushed to our website, replacing the existing content in the relevant field.

Is it possible to set up such a system? If so, could you please provide guidance on:

How to link specific Smartsheet form fields to corresponding website content areas
The process for implementing an approval workflow
Methods for automating the content update on our website once approval is granted

Any information on potential solutions, required technical setup, or limitations would be greatly appreciated.

Thank you for your assistance.

SSFeatures · 09/18/24

Hi Tania,

I’m going to lay out some ideas for how to make this work, but a lot of this is going to depend on how your website was developed, and how much flexibility you have to control your website. For example, if you built your website from scratch, then you can change anything that you want. If you use a website builder such as SquareSpace, then this might be more challenging.

# Setting up the sheet

Column “Approved” - A checkbox column. If the change has been approved, then the checkbox will be checked.

Column “Description” - A text column. Contains a description of the change.

Column “Content-ID” - A text column. Contains an ID that is used to identify which part of the website the change is for.

Column “Modified Date” - A system column that tracks when each row was last modified.

Some example “Content-ID”s could be something like “home-page-title”, “home-page-description”, “pricing-page-price-1-text”, “pricing-page-price-2-text”. Each of these Content-IDs must be unique across your entire website. Also, it might be a burden for your users to know exactly which Content-ID corresponds to which piece of your website. So you might make it so that the approver, who is responsible for approving the change, must manually enter the correct Content ID that corresponds to the change.

# Updating the website to use the content

If I was doing this for my website, I would probably create a giant JSON in its own file that will contain all of the content. The JSON will contain the Content-IDs and the actual content. For example:

{

“home-page-title”: “This is the title”

“home-page-description”: "This is the description”

}

Then my JavaScript would read the JSON and update all of the HTML based on the content in this file.

```

import {contentJSON} from “../content-json.json”;

const homePageTitle = document.querySelector(“#homePageTitle”);

homePageTitle.textContent = contentJson[‘home-page-title’];

```

# Pushing changes from the sheet to the JSON

I would create a program that scans the sheet every 30 minutes or so. The program would look for approved rows that have been modified since the last time that the program scanned the sheet. Then the program would update the JSON using the Content-IDs from the rows in the sheet.

If your team built your website from scratch programmatically, then you’re probably using a version control system like Git. In this case, the program will need to modify the JSON file push the change to your Git repo (e.g. GitHub).

# Alternatively - If you are using a website builder such as SquareSpace or you don’t want to check the JSON file into Git

An alternative approach is to host the JSON file, just like you would host images or other assets. Then the program wouldn’t need to check any code into any version control system, it would just need to override the existing file. If you are using a CDN, the API will most likely be S3, in which case you can use the S3 CLI or S3-Sync-Client to make this change. And it would be fairly straightforward.

If you do this approach, then you would need to modify the code from above to look something like this:

```

const contentJson = await fetch(URL_TO_WHEREVER_YOU_HOST_THE_FILE);

const homePageTitle = document.querySelector(“#homePageTitle”);

homePageTitle.textContent = contentJson[‘home-page-title’];

```

# Final Thoughts

Overall, this would be a tricky project but it is possible. It would require a lot of effort to get it going, and then it would require ongoing maintenance to keep it working.

I really loved this question though, and I’m happy to explore more ideas and I’m happy to further elaborate on anything that I mentioned above. Please let me know if you have any other questions.

SSFeatures

SSFeatures · 09/19/24

Hi Tania,

I’m going to lay out some ideas for how to make this work, but a lot of this is going to depend on how your website was developed, and how much flexibility you have to control your website. For example, if you built your website from scratch, then you can change anything that you want. If you use a website builder, then this might be more challenging.

Setting up the sheet

Column “Approved” - A checkbox column. If the change has been approved, then the checkbox will be checked.

Column “Description” - A text column. Contains a description of the change.

Column “ContentID” - A text column. Contains an ID that is used to identify which part of the website the change is for.

Column “Modified Date” - A system column that tracks when each row was last modified.

The ContentID column is the most important, it will be used to identify which content on the website the change is for. ContentIDs could be something like:

“home-page-title”, “home-page-description”, “pricing-page-price-1-text”, “pricing-page-price-2-text”.

Each of these ContentIDs must be unique across your entire website. Also, it might be a burden for your users to know exactly which ContentID corresponds to which piece of your website. So you might make it so that the approver who is responsible for approving the change, must manually enter the correct ContentID that corresponds to the change.

Updating the website to use the content

If I was doing this for my website, I would probably create a giant JSON object in its own file that will contain all of the content. The JSON will contain the ContentIDs and the actual content. For example:

{
“home-page-title”: “This is the title”
“home-page-description”: "This is the description”
}

Then my website would read the JSON and update all of the HTML based on the content in this file.

I would probably host the content JSON in the same place that I host my images and other assets. Then my website would fetch the JSON and update the HTML accordingly, for example:

const contentJson = await fetch(URL_TO_WHEREVER_YOU_HOST_THE_FILE);
const homePageTitle = document.querySelector(“#homePageTitle”);
homePageTitle.textContent = contentJson[‘home-page-title’];

Pushing changes from the sheet to the JSON

I would create a program that scans the sheet every 30 minutes or so. The program would look for approved rows that have been modified since the last time that the program scanned the sheet. Then the program would update the JSON using the ContentIDs from the rows in the sheet.

The program would load the existing JSON file, modify the JSON object, and then re-save the JSON file back to the place where I host the JSON file. In my case, I host my assets using a CDN that uses AWS’ S3 API. So I could use the S3 CLI or the S3-Sync-Client to override the JSON file with the new changes.

Alternatively, you might be able to use SmartSheet's Bridge to push the changes, depending on how you host the file.

Final Thoughts

Overall, this would be a tricky project but it is possible. It would require a lot of effort to get it going, and then it would require ongoing maintenance to keep it working.

If you want to make your entire website’s content editable, then the maintenance is going to be high since there will be a lot of ContentIDs. It would be difficult to keep track of which ContentID corresponds to which part of the page. However, if there are only a handful of pieces of content on your website that you want to be editable, the number of ContentIDs will be smaller and more maintainable.

I really loved this question though, and I’m happy to explore more ideas and I’m happy to further elaborate on anything that I mentioned above. Please let me know if you have any other questions.

SSFeatures

Andrée Starå · 09/19/24