Best Of
DataMesh within the same sheet to copy cells from one column to another
Up until today I've restricted my understanding of datamesh to copying values from sheet to sheet. However, I just discovered today that you can use it WITHIN a sheet! What does this solve?
If you ever need to copy a value from column A to column B, but do not want to use a formula, then this process is perfect. It also solves having to use data shuttle and excel to export/import a value back into the sheet.
For example, we have a backend process that generates a code for each line item and puts it in a column called [Generated Code]. The code is a concatenation of several things. We have another column called [Code] that is free text. The user has the option of putting something in that field, and most of the time they just forget. The data mesh trick copies the value from [Generated Code] to [Code] so the user never has to worry about it.
If in the future they want/need to make a manual change to the code, they are free to do that. I have data mesh set up to not overwrite values.
A second example has to do with start/end dates of customer events. Most of the time people forget to include an event's end date, and most of our events are 1 day events. This trick copies the start date to end date field. Obviously a formula cannot work here, because you could never have multiday events (without a helper field). This way we assume an empty end date means it's a one date event.
Just thought I'd pass this along, I hope it helps at least one other person.
Re: User invitation automatically declines
Hey everyone, we have an update!
Based on the experience this week with email security software, our Engineering team has implemented a temporary change for how our Invite emails function:
When the either the Decline or Accept options are selected in an invitation email, this link will redirect to the Smartsheet login page (instead of immediately taking the selected action).
- If the recipient has a Smartsheet account, they will need to log in to submit a response from within the application.
- If the recipient does not have a Smartsheet account, they will need to set a password. To set a password, they will need to select the Reset Password link and create a password that way.
Please re-send invitations to any of your users that show as Declined in your Admin Center and they should now be able to go directly to Smartsheet to Accept or Decline.
Thank you,
Genevieve
Dashboard - I didnt know this!
I have been using Smartsheet for years - and I am shocked that I am 1 day-old when I found out that this feature exists. Clicking on the legends will change the charts and graphs accordingly! Oh well - learning something new everyday (and this is what I love about Smartshweet!) Do you know any other cool features on dashboards?
Best way to learn?
New to Smartsheet. Can anyone weigh in on an overall learning path - free, paid, Smartsheet University? I'm in a Project Management role in my organization and want to move away from excel to smartsheet as our primary tool
Create dynamic custom schedules based on selections in intake
Hi all, I recently developed a cool solution to an issue that we had... how to build a custom schedule based on selections in an intake form. This solution leverages Bridge, but you could do the same thing using a third party integration tool.
THE GOAL
The process goal for this solution is:
- Collect project requests using an intake form
- Allow the requestor to specify what types of work need to be done (ie content creation, forum hosting, website uploads). This was for Marketing, but could be used for any type of work.
- Produce a custom task schedule, based on the type of work selected. For example, if the requestor selected "Brand - Brand review" the custom task schedule would have tasks related to Brand review only.
- Set the start date of the tasks to the start date from the request
THE SOLUTION SUMMARY
When someone submits a form and selects certain components for their task sheet, Bridge triggers. Bridge then copies a template sheet which contains all possible components and tasks. Then Bridge deletes all the task sections that don't match the components selected on the form.
The reason I copy a sheet and then delete sections, rather than copying over only selected rows, is because of Predecessors. When doing a row copy, the Predecessors are not copied over. However if doing a sheet copy, the Predecessors are kept intact.
THE SOLUTION DETAILS
I created an Intake sheet with the following columns:
- Project Name
- Requested Start Date
- Task Sheet Link
- Components: this column is a multiple-selection dropdown with the name of each type of work
I created a Task Template sheet with the following setup:
- A row at the top with the name "Requested Start Date" in the Primary column
- A column called "Row Type" with the column formula =IF(COUNT(ANCESTORS([Task Name]@row))=0,"Section Header","Task")
- A column called "Section" with the column formula =IF([Row Type]@row="Section Header",[Task Name]@row, INDEX(ANCESTORS([Task Name]@row),1))
- A set of tasks, grouped under parents, with each parent matching the name of a Component in the Intake Sheet. For example, one section of tasks was called "Brand-Brand review" to match to the component option, with subtasks indented underneath that outlined the steps and tasks needed to complete Forum setup. Note you could have multiple levels of hierarchy, the important thing is that the top parent names match the Component field options in the Intake form.
- I put the Predecessor for the first task of each section to be 1. That linked the first task in each section to the top row where I have the requested Start Date.
I created a Bridge Smartsheet Integration trigger to kick off a workflow when a row was added to the Intake Sheet.
I created a Bridge workflow to be the "child" workflow to do the deletions of each section of tasks from the copied template that doesn't match the selected components in intake. You need to create this "child" first, then the parent, because the parent calls the child in the last step. Note that this workflow won't do anything until you run the parent by triggering a new entry in your intake sheet.
- Conditional Junction: Contains. This checks to see if the first section of copied tasks matches a component selected in the intake sheet. Name = Component Selected. Value = {{runtime.entities.components}}. Contents = {{runtime.data.cells.Task Name.value}}
- true side of junction: State Name = Selected. Nothing else after this step on the true side.
- false side of junction: State Name = Not Selected. Remaining steps fall under the false side of the junction
- Search Sheet: this finds all row IDs for tasks in the section. Sheet = {{runtime.entities.sheedId}}. Row Filter 1 = Section Is Equal To {{runtime.data.cells.Task name.value}}
- Array Mgmt: Extract Field From Array: this collects the row IDs into a single array. Array = {{states.Not Selected.smartsheet.search_sheet.sheet.rows}}. Key = id
- Delete Row: The last step, this removes the entire section that was not selected in the Intake Sheet request. Sheet = {{runtime.entities.sheetID}}. Row ID={{states.Not Selected.arraymgmt.extract_from_array.results}}
Then finally I created the parent Bridge workflow with these steps:
- Smartsheet: Get Row: returns the row that was sent by the trigger (the newly added row) from the Intake Sheet. This is to get all the detailed data from the row. Sheet = {{runtime.sheetID}}. Row ID={{runtime.event.id}}. Advanced Options = Include Row Permanent Link
- State: Copy Template
- HTTP Call: copies the template to a folder and renames the copy to match the requested project name. URL = https://api.smartsheet.com/2.0/sheets/your template sheet id/copy?include=attachments,cellLinks,data,discussions,filters,forms,ruleRecipients,rules. Method=POST. Headers= Authorization (your Bearer token) and Content-Type (application/json). Body = {"destinationType":"folder","destinationId":your desired destination folder id,"newName":"{{states.startstate.smartsheet.get_row.row.cells.Project Name.value}}"}
- State: Update Copy with Start Date
- Search Sheet: gets the row ID of the first row. Sheet = {{states.Copy Template.utilities.httpcall.result.id}}. Row Filters = Section Is Equal To "Requested Start Date". Advanced Options = Include Sheet Data
- Update Row: sets the start date based on the request. Sheet = {{states.Copy Template.utilities.httpcall.result.id}}. Row ID = {{states.Update Copy with Start Date.smartsheet.search_sheet.sheet.rows.0.id}}. Cells Key 1 = Start. Cells Value 1 = {{states.startstate.smartsheet.get_row.row.cells.Requested Start Date.value||2023-01-01T08:00:00}}. (I used 1/1/23 as a default start date if no start date was given in the request)
- State: Update Sheet Link
- Update Row: posts a link to the newly created sheet, back into the Intake sheet for reference and easy navigation. Sheet = {{runtime.sheetID}}. Row ID = {{runtime.event.id}}. Advanced Cells 1 Column = "Task Sheet Link". Input Type = Hyperlink. Hyperlink Display Text = {{states.startstate.smartsheet.get_row.row.cells.Campaign Name Trimmed.value}} Task Sheet. Hyperlink Sheet ID = {{states.Copy Template.utilities.httpcall.result.id}}
- State: Get Section Row IDs from Copy
- Search Sheet: gets a list of all the section top header names. Sheet = {{states.Copy Template.utilities.httpcall.result.id}}. Row Filter 1 = Row Type Is Equal To Section Header. Advanced Options = Include Sheet Data
- Child Workflow: Remove Sections. This step calls a child workflow which will parse each section header name and compare it to the list of selected components in the intake sheet request. If there's a match, the section of tasks is kept in place. If not matching, the section is deleted. Child Workflow Name = the name of the child workflow you created above. Number of Runs = {{states.Get Section Row IDs from Copy.smartsheet.search_sheet.sheet.rows}}. Child Entity Key 1 = components. Child Entity Value 1 = {{states.startstate.smartsheet.get_row.row.cells.Components.displayValue}}. Child Entity Key 2 = sheetID. Child Entity Value 2 = {{states.Copy Template.utilities.httpcall.result.id}}
Whew!!! I know it's a lot, but the end result here is that you'll get a sheet created for each request, with the task sections that were requested. Predecessors will be intact, the start date will be set, a link will be posted back to the request row, and now your team can move forward with doing the work!
Scheduling automatic reminders for Proof reviewers
Here is a way to create reminders for Proof reviewers. This solution will:
- Send a reminder 5 days after the initial invite, if no one has responded to a Proof review request.
- Send a reminder 5 days after the last reminder, if no one has responded to a Proof review.
- Reset when a new Proof version is created.
Some useful modifications include:
- Reminding any one who has not responded to a Proof review request.
- Changing the reminder frequency for urgent Proofs or conditions.
To create this solution, follow these steps:
- Create the Proof Info columns. These will reveal information such as who has responded, whose response is pending, and status of the proof.
- Create these columns
- Invite sent date.
- Date column.
- Will be filled with automation when invites are sent.
- Date column.
- Workdays since invite sent.
- Formula
- =IF([Invite sent date]@row = "", "", NETWORKDAY([Invite sent date]@row, TODAY()) - 1)
- Send proof review reminder.
- Trigger to send reminder notification. In this case the reminded is sent after 5 days if no one has responded.
- Formula
- =IF(AND([Workdays since invite sent]@row >= 5, [Contract draft status]@row = "In Review", OR([Workdays since reminder sent]@row = "", [Workdays since reminder sent]@row >= 5)), "Yes", "No")
- Last reminder sent.
- Date column.
- Will be filled with automation when a reminder is sent.
- Workdays since reminder sent.
- Formula
- =IF([Last reminder sent]@row = "", "", NETWORKDAY([Last reminder sent]@row, TODAY()) - 1)
- Create two automations
I hope this helps.
Neil Egsgard
Business Solutions Architect
Southern Alberta Institute of Technology
TIP - Find the date of the first Sunday of the month
This little formula is useful for a range of solutions, like working out if a date is in daylight savings time or not. The examples below reference a date field named [Date].
The first part of the formula finds the date of the seventh day of the month. This is done so that the number of days from Sunday can be subtracted:
= DATE( YEAR( [Date]@row ), MONTH( [Date]@row ), 7 )
The second part of the formula finds the number of days to subtract from that date:
- ( WEEKDAY( DATE( YEAR( [Date]@row ), MONTH( [Date]@row ), 7 ) ) - 1 )
This part of the formula gets the date of the seventh day of the month again, and works out what day of the week it is. We subtract 1 from the result because the WEEKDAY() function returns values starting at Sunday = 1. So if the seventh day of the week just happens to be Sunday we don't want to subtract 1, we need to subtract 0 instead.
So the entire formula to find the date of the first Sunday of a month is:
= DATE( YEAR( [Date]@row ), MONTH( [Date]@row ), 7 ) - ( WEEKDAY( DATE( YEAR( [Date]@row ), MONTH( [Date]@row ), 7 ) ) - 1 )
This can be tweaked a little for applications like checking if a date is/was in daylight savings time. This example expects daylight savings time to start on the first Sunday in October and end on the first Sunday in April of the next year:
=IF( AND( [Date]@row >= DATE( YEAR( [Date]@row ), 10, 7 ) - ( WEEKDAY( DATE( YEAR( [Date]@row ), 10, 7 ) ) - 1 ), [Date]@row < DATE( YEAR( [Date]@row ) + 1, 4, 7 ) - ( WEEKDAY( DATE( YEAR( [Date]@row) + 1, 4, 7) ) - 1) ), 1, 0)
It checks to see if [Date] is greater than or equal to the first Sunday in October (month 10) in the same year as [Date] AND is less than the fist Sunday of April (month 4) in the following year. If that is true then it returns 1 (daylight savings), otherwise 0 (not daylight savings).
To find the date of the first day of the month for some other weekday a slight tweak is required on the second part of the formula:
= DATE( YEAR( [Date]@row ), MONTH( [Date]@row ), 7 ) - MOD( ( WEEKDAY( DATE( YEAR( [Date]@row ), MONTH( [Date]@row ), 7 ) ) - 1 ) + [Weekday], 7)
where [Weekday] contains:
- 0 for Sunday
- 1 for Monday
- 2 for Tuesday
- 3 for Wednesday
- 4 for Thursday
- 5 for Friday
- 6 for Saturday
Re: How to extract just the month name from a date
Nested IFs are useful when you need the full month name, but for three digit month names you can use a much simpler formula:
=MID("JanFebMarAprMayJunJulAugSepOctNovDec", (Mth@row * 3) - 2, 3)
Re: FORMULA TO PRESENT THE MONTH (NAME) IN A CELL
If you only need three digit month names the formula is much simpler:
=MID("JanFebMarAprMayJunJulAugSepOctNovDec", (Mth@row * 3) - 2, 3)
Re: Month Name written as Text for Today's Date
Nested IFs are useful when you need the full month (eg. January) because they are all different lengths.
But for three digit abbreviations you can use a much simpler formula:
=MID("JanFebMarAprMayJunJulAugSepOctNovDec", (Mth@row * 3) - 2, 3)