The "Change Cell Value" action when doing automation leaves a lot to be desired. Currently it seems to only be able to affect Text/Number, Dropdown, and Checkbox columns. There is a separate action to fill out a date for Date cells, but that is also very restrictive in that it only can enter the date of when the automation is triggered. The idea is to combine those into one function and vastly improve the functionality of the action.
The improved method would be able to affect Text/Number, Dropdown, Date, Contact, Checkbox, and Symbols columns. Each column would have different functionality based on the column type and additional settings for the column. Below is a breakdown of proposed features for each column type:
- Specific Input: Enters a specific value into the cell, specified by the user setting up the automation. When the automation runs it will always enter the specified value.
- Copy from Cell: Enters a value into the cell based on the value of another column and that column's type.
- Text/Number - Tt would just copy the value over as normal.
- Dropdown - It would enter the selected dropdown(s). If that Dropdown allows multiple entries the user can specify a symbol as a separator such as ; or | symbol
- Date/Created Date/Modified Date - It would copy the date over as a text string. If the column includes a timestamp the user can choose to include or omit the time.
- Contact List - User can choose to include the name of the contact, the email, or both. Similar to the Dropdown column, user can set a separator symbol for multiple entries.
- Checkbox/Symbols - Copies the text value of the cell. For example, if a Checkbox is checked, it would copy over the text value of "True", if using the Symbols set to Red/Yellow/Green and the cell is Green it would copy the text value of "Green". These already function like this for manually copying the cells, this just adds support to automate it.
- Auto Number - Copies the value over as a text string.
- Specific Input: Enters a specific value into the cell, same as it does for Text/Number. However this can only be selected if the column you updating has "Restrict to XXX only" unchecked. Will store it as a text string. For Contact List columns, this will store as the Contact's Name.
- Acceptable Value: Can select an acceptable value based on the column type. Dropdown lets you select only from the values in the column's settings, Date gives you a date picker (including "Date Automation Runs" as an option), Checkbox gives you Checked (True) and Unchecked, and so on.
- Copy from Cell: Enters a value into the cell based on the value of another column and that column's type. Has additional options below:
- Restrict to same Column Type? - If yes (this is the default value), will only allow selecting a column type that matches the column being updated. If no, the "Attempt to Convert to Matching Data Type?" option becomes available to be selected.
- This cannot be set to "no" unless the column you are updating has "Restrict to XXX only" turned off.
- For Date columns, you can select Created By or Modified By columns to copy from even when this is set to "Yes", however the timestamp will be omitted.
- Attempt to Convert to Matching Data Type? - Only if "Restrict to same Column Type" is set to "No". If yes, Smartsheet will attempt to parse the value it is copying over and convert it to a valid input for the column you are editing. For example, if you have a text field that says "True" and are trying to copy it to a Checkbox, it will parse that and convert it to simply checking the Checkbox. If no, it just enters it as a text string.
This would give a lot of functionality to automation, some examples of where this would come in handy from my personal experiences:
- Being able to copy a date from one column to another if a cell matches a specific criteria, without needing to use a column formula for the entire column. Currently this cannot be done with anything other than the day the automation runs so it doesn't work for historical data.
- Being able to parse out a normal date from a Created/Modified Date systematically.
- Being able to pull data from a different column that changes regularly to be able to reference back to the column's original value for different logic flows or formula-driven updates.