Best Of

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.

1. Resource Management does not allow you to export the archive status or archive date of a project
   1. This should be fixed.
   2. This could be possible this is possible through Bridge or integration.
2. You can see a list of archived projects using the Project Portfolio
   1. https://rm.smartsheet.com/projects
   2. Set “Project State” to “Archived”
   3. 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.
3. You can export a list of Projects with “Project State” = “Active”
   1. https://rm.smartsheet.com/addmultipleproject
   2. Click “Export project list”
4. You can identify if a project is archived using that export with the following method.
   1. Overview
      1. Checks if project is on the most recent Project List export and, if not, marks it as archived.
   2. Create a Smartsheet grid sheet to receive the Project List
      1. Include all fields from the Project List export.
      2. Include “Modified” and “Modified by” fields.
      3. Add these two fields.
         1. “Last updated by Data Shuttle”
            1. Date field
            2. Records date record was modified by Data Shuttle
         2. “Archived”
            1. Indicates whether record is an Archived project
   3. Data Shuttle – Load Project List into Smartsheet
      1. Create a Data Shuttle that loads the Project List into Smartsheet grid above
      2. Data Shuttle must be owned by a user that only adjusts sheets through data meshes, or data shuttles.
   4. Create an automation on the Smartsheet grid above
      1. Automation updates the “Last updated by Data Shuttle” field if:
         1. Last modified user = User that owns the Data Shuttle from above
   5. Put the following formula into the “Archived” field
      1. =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")

Regards,

Neil Egsgard

Business Solutions Architect

Southern Alberta Institute of Technology

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.

I'm was using Generate Form to create a PDF I needed. While I was getting the content I needed, the font size in the form was wonky and inconsistent. The first 3 fields were in LARGE FONT size while the rest were a standardized. I don't know why or how the this happened. I was able to find a solution after an hour + of sleuthing. In hopes of saving others the hassle, I've attached a quick how to guide.

Prior to getting Datamesh, I devised a solution to transfer data from one page to another, without having matching columns. This process uses an intermediary sheet. It's kind of a pain to set up, but it works well and requires little to no maintenance. I still find some very small use cases where this comes in handy. Thought I would share.

Below is an example of a source and destination sheet, with an intermediate "cleaner" sheet. Requirements:

• The source sheet and the cleaner must have a reference column with shared unique IDs. I'll tell you how to manage that below.
• The cleaner sheet must have the same column headers as the target sheet.

Setting up matching unique IDs between source and cleaner sheets

You may have an active sheet with data constantly being added. You may not even have unique IDs associated with the data, or they may be unpredictable. What you need is a predictable unique Id in the source sheet. To get that, set up a Row ID column. This will provide a sequential row ID for every incoming row. In the above example the row IDs are 1-5.

Then, in the cleaner sheet, add a list of numbers to cover both your known rows (i.e. "1-5") and enough future rows so that you aren't having to constantly go back and add more. I usually load about 3,000 at a time, and I don't have to look at them for a few months. You can easily create a column of numbers in excel and then copy and paste them into your cleaner's reference column, 500 at a time.

Mirroring Data in your Cleaner Sheet

Now that you have a unique id to use as a reference, use cross-sheet column formulas to bring over the data you need, in the format you need it in. In the cleaner sheet example, I merge the first and last name into one column to match the target sheet. I also remove the color from the house information. The formula for "Client Name" would look something like this:

=INDEX(COLLECT({source_firstname}, {source_rowid}, [Extra Column]@row),1) + " " + INDEX(COLLECT({source_lastname}, {source_rowid}, [Extra Column]@row),1)

Send the Data Via Automation

Use an automation to transfer the data to the target sheet. A few things to keep in mind:

• You may need to set some conditional cross-sheet logic to trigger your information to appear at the correct time. For instance, you may only want the data to transfer once a checkbox on the source sheet has been checked. You can set your cleaner sheet up so that it only populates data if the corresponding checkbox is checked, or other condition is met.
• It's up to you whether you want to use a MOVE or COPY command. I've found that using a MOVE command is nice because it essentially deletes the row ID from the cleaner sheet and makes it impossible to accidentally send more. On occasion, I've added an ID back in to re-do a transfer. Using "COPY" command would keep the row ID on the cleaner page, but you may need to have further logic to keep from re-triggering the automation and sending the row again.
• You can't trigger an automation using a formula (they do this to keep from accidentally triggering an automation repeatedly). However, you can set a time-based automation to send based off a formula column. I had one instance where I set up 12 of the same time-based automations, each for a different hour. Classy, I know.

Handy Trick

Remember, your cleaner column headers must exactly match your destination column headers. And your move command will send every column, including the row ID column on your cleaner page. However, move automations can't overwrite column formulas. So if you name your row ID column in your cleaner sheet the same as a column in your target sheet that's got a column formula assigned, all the data will copy or move over except the row ID, as that will essentially fail to send.

Use Cases

There are plenty of use cases for this trick. I originally devised it because I had to have a continuous merge of data from three separate sheets that didn't look at all alike. I used a cleaner sheet to parse all that data. In that case, I actually had three sets of unique IDs, one to match each sheet. Complicated? Yes. But it solved an impossible problem, so there you go.