Best Of

Hello everyone!

I spent WAY to long looking for this answer so I thought I would share it with the Community in hopes of helping someone else.

Problem: Adding attachments to rows/sheet is not possible through the standard SmartSheet Connector

Solution: This can only be done by creating a Custom Connector. It CANNOT be done through the HTTP request. As of writing this, here is the relevant information. On the

General tab

Scheme: "HTTPS"

Host: "api.smartsheet.com"

Base: "/2.0/"

Security

Authentication Method: API Key

Parameter Location: Header

The rest of the info is up to you.

Definition

Url: https://api.smartsheet.com/2.0/sheets/{sheetId}

Headers: Content-Type and Content-Disposition

Body: This must be edited in the swagger editor after creating Action. The body should look like this

" - name: file

      in: body

      required: false

      schema:

        type: string

        format: binary

"

Be sure to format it consistent with the rest of the Yaml.

The rest of the info is up to you.

Finally Update the connector. You should now be able to use this custom connector in your flows.

Your Atachtion should look like the example below. Key items, you will need to know the Content-type. If you are using ShareFile, you can get it directly from the "Get File Content" step. Just be sure to drill down from the body, ie. @{body('Get_file_content')?['$content-type']}. I used the Get File Metadata step to get the file name. You will need the file extension but not the folder path. Finally, the File MUST be in Binary. If your file is in Base64, use the following: @{base64ToBinary(body('Get_file_content')?['$content'])}

I hope this helps!

I've seen a lot of posts with people trying to figure out how to parse out predecessors so that they can set the status of the next task to "Ready" or something similar. I came up with a solution that worked for me, I figured I'd share it. This solution takes care of the various dependency types and lead/lags.

1) Create an Auto Numbering column ("ID")
2) Create a Column to get row numbers ("Index"): MATCH(ID@row, ID:ID, 0)
3) Create a row number column for each non-FS predecessor type you plan to use:
a) FF Index = Index + "FF"
b) SS Index = Index + "SS"
4) Create a multi-select Helper column ("Dependency Helper"): SUBSTITUTE(SUBSTITUTE(Predecessors@row, "FS", ", "), ",", CHAR(10))

5) Create multi-select columns for each dependency type you plan to use:
a) FS Dependencies: JOIN(COLLECT(Index:Index, Index:Index, HAS(Dependencies@row, @cell)), CHAR(10))
b) FF Dependencies: JOIN(COLLECT([FF Index]:[FF Index], [FF Index]:[FF Index], HAS(Dependencies@row, @cell)), CHAR(10))
c) SS Dependencies: JOIN(COLLECT([SS Index]:[SS Index], [SS Index]:[SS Index], HAS(Dependencies@row, @cell)), CHAR(10))

You will now have all of your dependencies separated by type and all lags removed. You can use this to build out status logic however you'd like. Below is an example for setting a Task to "Ready" if all FS Predecessors are 100% complete and all SS Predecessors are greater than 0% complete:

=IF(COUNTM([SS Dependencies]@row, [FS Dependencies]@row) = COUNTIFS(Index:Index, FIND(@cell, [FS Dependencies]@row) > 0, [% Complete]:[% Complete], =1) + COUNTIFS(Index:Index, FIND(@cell, [SS Dependencies]@row) > 0, [% Complete]:[% Complete], >0), "Ready", "Queued")

Cheers!

Vince K.

vince@nnaviaconsulting.com
https://www.linkedin.com/in/vineshkapadia