Best Of
Auto-Create Child Rows WITHOUT Bridge or API
This initial solution is based on form submissions being made and each form submission triggering the creation of a set of "template" child rows per submission WITHOUT the need for a 3rd party app, the premium add-on Bridge, or the API.
So if your form submissions look like this:
Your working sheet could look like this:
There are a number of modifications that can be made such as the number of children, the number of levels, column data in the child rows, etc.. The below is just the basics to help everyone get started.
So here we go...
On the sheet where your form submissions will be made, you will need two sets of columns. One set that has the data to be pushed over, and another set that collects the data from the form submissions. You can leverage custom field titles in the form so that the form looks decent but the sheet stays easier to organize/manage. You are also going to need an auto-number column.
At the top of the form sheet and utilizing the primary column, you will put in your parent/child "template" set.
有很多公式,可以在th拉e most recent form entry into that parent row, but for the sake of this example I have used
=INDEX([Form Field A]:[Form Field A], MATCH(MAX([Auto-Number]:[Auto-Number]), [Auto-Number]:[Auto-Number], 0))
Of course that same formula is tweaked and used in the B and C columns pulling from the corresponding Form Field columns.
The final step is creating a Copy Row automation. Set it to trigger whenever the A column changes (or a different column or combination of columns used in the top row of the template to accommodate your needs). The action would be to Copy Row to your Working Sheet.
Additional screenshots are below. This is just the base idea and can be expanded on in a number of ways, but accommodating every single possibility just isn't feasible in a single post.
Date Formats in Worksheet
Just for knowledge -
Someone had raised this question about date formats in the community and I wanted to add the response to it so it might help some others as well. A mini disclaimer here that most of you might already know this but sometimes we all forget the most obvious things that are in front of our eyes so just take this in jest and don't hate me for pointing out the obvious! :)
So, in case any of you are looking to change the date format in your working sheet to anything different from your computer/regional settings, the easiest way is to go to settings in the left hand panel and hit the regional preferences option. Here you can modify the date format and your sheet/forms etc. will reflect accordingly.
Hope this helps :)
Cheers!
Ipshita
Re: SUMIF within the last 30 days?
Hi. Do you have a date field in one of your sheets? You'll need to convert this to a SUMIFS, and include the date field as part of your calculation.
Let's say your Flight Activity Report looks like this:
On your summary sheet, where you are calculating flight time in last 30 days, you can use this formula:
=SUMIFS({Flight Activity Report Flight Minutes}, {Flight Activity Report Pilot}, Pilot@row, {Flight Activity Report Flight Date}, >=TODAY(-30))
Placing this formula in the "Minutes Flown in Last 30 Days" column will calculate the total flight time for each pilot in the Pilot column for the past 30 days.
You can alter this to suit whatever columns you're working, but I encourage your to name your cross-reference ranges instead of using Smartsheet's default values of "Range 1", "Range 2", etc. That way, you can more easily find errors in your formulas and you can visually track the logic of them later.
Use workflows to streamline your approval process
Suppose you work in a marketing department, and your team creates various types of content, such as blog posts, social media posts, and email newsletters. You want to establish a streamlined approval process to ensure that all content goes through the necessary stages before publication. Here's how you can set up a content approval workflow in Smartsheet:
Define Workflow Stages (Statuses):
- Draft
- Review
- Revision Needed
- Final Approval
- Published
Create Columns:
- Content Title
- Assigned To (for assigning tasks to team members responsible for the review)
- Due Date
- Status (dropdown column with the defined workflow stages)
- Comments (for feedback and communication during the review process)
Create Workflow Rules:
- Rule 1: When the status is set to "Draft," assign the task to the relevant team member for the initial review and set the due date.
- Rule 2: When a team member sets the status to "Review" and adds comments, automatically change the status to "Revision Needed" if any revisions are required.
- Rule 3: When a team member sets the status to "Final Approval," assign the task to the appropriate approver for final review and set the due date.
- Rule 4: When the approver sets the status to "Published," notify the team that the content is approved and ready for publication.
Collaborate and Track Progress:
- Team members can access the Smartsheet, review assigned tasks, and update the status and comments as needed.
- Approvers can easily view pending tasks, provide feedback, and approve the content for publication.
By implementing this content approval workflow in Smartsheet, you can streamline the review process, ensure proper feedback and revisions, and maintain visibility on the progress of each piece of content. The automation saves time by assigning tasks, triggering notifications, and keeping everyone involved in the loop. Additionally, the history and comment features in Smartsheet enable transparent communication and provide an audit trail of the entire approval process.
Bulk delete sheet rows using Windows Powershell
There is not currently a method for bulk deleting rows using the Smartsheet API (that I could find) so thought I would share what I came up with. Deleting single rows or small chunks of rows for sheets with thousands of rows is slow. I was able to use the example found here as inspiration...https://community.smartsheet.com/discussion/88154/how-to-delete-all-the-rows-of-a-sheet-using-python-at-once
The basic idea is to to do a PUT rest call that sets each rows parentID property equal to the row id of a parent row. I've tested this with a sheet that had 20000 single column rows. You can do this in smaller chunks if needed by setting the chunk size. It then deletes the parent rows which also deletes their child rows in a single DELETE rest call.
You will need to populate your API Key and the ID of the target sheet for the script to work.
Hopefully someone finds this helpful.
Powershell Script:
function DeleteSheetRows([object]$sheet){
$sheetID = $sheet.id
$rowsurl = "$APIurl/sheets/$sheetID/rows"
$rows = $sheet.rows
# bail if there are no rows in the sheet
if ($rows.Length -eq 0) { return }
# Deleting rows individually or in small chunks is very slow.
# We will first move large chunks of rows as children under the first row in the chunk.
# We will store the ids of the parent rows until all rows are nested.
# Then we will delete the parent rows which also deletes the child rows.
$r1 = 0
$chunksize = 20000 # this is the largest number of rows tested. There is no limit that I know of on the smartsheet ARRAY[] size.
$r2 = $r1 + $chunksize - 1
$name = $sheet.name
$parentIDs = [System.Collections.ArrayList]::new()
while ($r1 -lt $rows.Count) {
# retrieve subset of rows as a chunk.
$IDs = $rows[$r1..$r2].id
$rcount = $IDs.Count
# setup children and parent row json data.
$parentID = $IDs[0]
$childIds = $IDs[1..$IDs.Count]
$json = $childIDS | % {
[PSCustomObject]@{
id = $_;
parentId = $parentID
}
} | ConvertTo-Json -Depth 10
# move the child rows into the parentRow.
$result = Invoke-RestMethod -uri $rowsurl -Method Put -Headers $put_headers -Body $json
if ($result.message -eq "SUCCESS") {
$rcount = $IDs.Count
write-host $rcount "rows nested into parent"
} else {
write-host $rcount " - " $result.message
}
# increment the chunk
$r1 += $chunksize
$r2 += $chunksize
# store the parent id. We will delete all the parents (and their children) at a later step.
$parentIDs.Add($parentID.Tostring())
}
# delete the parent rows
$rurl = "$url/sheets/$sheetID/rows?ids="
$rowstoDeleteUrl = $rurl + [string]::join(',', $parentIDs.ToArray())
$rowstoDeleteUrl += "&ignoreRowsNotFound=true"
$result = Invoke-RestMethod -Method Delete -Uri $rowstoDeleteUrl -Headers $get_headers
if ($result.message -eq "SUCCESS") {
$rcount = $rows.Count
write-host $rcount "rows deleted from $name"
}
# cleanup (if you don't these can cause big memory leaks in large scripts)
$sheet = $null
$rows = $null
$json = $null
$IDs = $null
$parentIDs = $null
$childIds = $null
}
$timer = [Diagnostics.Stopwatch]::StartNew()
# set access variables for smartsheet
$apiKey = ""
$APIurl = "https://api.smartsheet.com/2.0"
$get_headers = @{"Authorization" = "Bearer " + $apiKey}
$put_headers = @{}
$put_headers.Add("Authorization", "Bearer " + $apiKey)
$put_headers.Add("Content-Type", "application/json")
$sheetID = ''
如果(apiKey eq”——或者sheetID——美元eq '') {
throw "Make sure the apiKey and sheetID variables are populated before running script"
}
$surl = "$url/sheets/$sheetID"
$sheet = Invoke-RestMethod -uri $surl -Headers $get_headers
DeleteSheetRows $sheet
$timer.elapsed.totalseconds
$timer.Stop()
Remote Work - Thoughts
Hello community,
I am curious how many community members are currently working remote, in the office, or some sort of hybrid, and also know what type of industry they are working in.
I work in local government, which is a sector that *tends* to be less innovative and forward thinking than private business, but to be fair this is not always the case in every state and/or city. That being said, our department head has very recently allows us to begin working a hybrid schedule.
作为一个业务系统分析师,我可能需要provide support to 11 different departments throughout the city (Police, Fire, Parks & Rec, etc.) in any given week. Working remotely allows me to save a ton of time (and fuel) commuting across town from department to department. We know that obviously not every issue can be resolved remotely, and when that's the case we will obviously facilitate in person meetings.
I would also say that if the project is particularly CREATIVE in nature (ex. new software implementation), I typically advocate for these meetings to be held in person versus remote. I've found this encourages more connection and collaboration from everyone in the room. On the other hand, if the other more routine day-to-day tasks can be done remotely, allow employees the flexibility to complete those assignments remotely if it works better for them.
Would love to hear others thoughts.
TK
Re: IF THEN formula
@JBolanSpacing in formulas can be a crapshoot, but I find there is generally not an issue having spaces around operators and after commas within a formula; it also aids in readability. Smartsheet likes it this way too, and will even add spacing automatically. For instance, I can type this as a formula:
and once I hit enter, I see the correct result... and also that Smartsheet has corrected the spacing:
I would say the chances of Smartsheet changing this to match Excel is effectively ZERO. Smartsheet's spacing is far easier to read and use.
If you have a suggestion for improving tool tips, by all means share itHERE, but I'm not sure how much better it can get than what shows up for each function after you type the function and the opening parentheses. I mean, you've got the syntax, an example, a summary, an explanation of the arguments, and a link to the function's help page allright there!The highlighted portion of the syntax even shows you what part of the formula you are currently in!
BTW, you can bookmark the links in my signature for the function help pages and the formula error message page. There also the awesome公式手册!
Re: Consolidate unique values from multiple columns into single column
I turned off the publishing because there was another more efficient solution presented by someone else that uses the SUBSTITUTE function and adjusting the placement of the SUBSTITUTE to essentially place a locator. I must have forgotten to include a link to that thread when I did that. I can't remember who posted it though, but I have tweaked it a bit.
First you will need a text number column (called "Number" in this example). In this column you would manually enter the numbers 1 through however many you will need.
Then in the column you want to parse the list down, you would use something like this:
NOTE: This is under the assumption that you will not have ! (exclamation point) or | (pipe) in any of the data you are wanting to pull through. If you are, then you will need to use different punctuation in the formula. This is also assuming that the list you want to remove duplicates from is all in a single column. You may need to adjust the List:List range if you are pulling from multiple columns.
=IFERROR(MID("!" + JOIN(DISTINCT(COLLECT(List:List, List:List, @cell <> "")), "!") + "!", FIND("|", SUBSTITUTE("!" + JOIN(DISTINCT(COLLECT(List:List, List:List, @cell <> "")), "!") + "!", "!", "|", Number@row)) + 1, FIND("|", SUBSTITUTE("!" + JOIN(DISTINCT(COLLECT(List:List, List:List, @cell <> "")), "!") + "!", "!", "|", Number@row + 1)) - (FIND("|", SUBSTITUTE("!" + JOIN(DISTINCT(COLLECT(List:List, List:List, @cell <> "")), "!") + "!", "!", "|", Number@row)) + 1)), "")
Formula is in [Column2] of the below snippet:
Getting list of archived projects from Resource Management
Here is how you can currently get a list of archived projects from Resource Management. This solution does not explore Bridge or integrations using API.
- Resource Management does not allow you to export the archive status or archive date of a project
- This should be fixed.
- This could be possible this is possible through Bridge or integration.
- You can see a list of archived projects using the Project Portfolio
- https://rm.smartsheet.com/projects
- Set “Project State” to “Archived”
- There is no good way to export this data. Copy and paste into excel puts each value on a row so additional work is required to make the paste useful.
- You can export a list of Projects with “Project State” = “Active”
- https://rm.smartsheet.com/addmultipleproject
- Click “Export project list”
- You can identify if a project is archived using that export with the following method.
- Overview
- 检查项目在最近的项目st export and, if not, marks it as archived.
- Create a Smartsheet grid sheet to receive the Project List
- Include all fields from the Project List export.
- Include “Modified” and “Modified by” fields.
- Add these two fields.
- “Last updated by Data Shuttle”
- Date field
- Records date record was modified by Data Shuttle
- “Archived”
- Indicates whether record is an Archived project
- “Last updated by Data Shuttle”
- Data Shuttle – Load Project List into Smartsheet
- Create a Data Shuttle that loads the Project List into Smartsheet grid above
- Data Shuttle must be owned by a user that only adjusts sheets through data meshes, or data shuttles.
- Create an automation on the Smartsheet grid above
- Automation updates the “Last updated by Data Shuttle” field if:
- Last modified user = User that owns the Data Shuttle from above
- Automation updates the “Last updated by Data Shuttle” field if:
- Put the following formula into the “Archived” field
- =IF([Last updated by Data Shuttle]@row <> MAX(COLLECT([Last updated by Data Shuttle]:[Last updated by Data Shuttle], [Last updated by Data Shuttle]:[Last updated by Data Shuttle], <>"")), "Archived", "No")
- Overview
Regards,
Neil Egsgard
Business Solutions Architect
Southern Alberta Institute of Technology
Copying Rows and their attachments work around
Currently Smartsheet doesn't have the ability to copy rows including their attachments.
I've found a workaround that is much quicker than uploading all the attachments individually on other rows. This is especially useful if you have MANY rows that you need to copy including their attachments.
Save your sheet as a new sheet. On the new sheet, create an automation where whenever a row is changed, copy that row to your original sheet. Then you can just add a period or something to all the rows you need to copy. They will all move to your original sheet with the attachments. Drag those rows to where they need to go once on your original sheet.
This workaround saved me a ton of time. I hope this helps someone.