Sheets

Hazika Reshi · 06/30/23

I am trying to convert my excel spreadsheet into a Smartsheet and I want to be able to attach pdfs of receipts that exist on my Dropbox folders to each of the relevant cells in Smartsheet. The data is worth 6 years (a lot!). Is there a way or a code that I can use so that I don't have to attach all the files manually one by one as this would literally take me months.

Andrée Starå · 06/30/23

Hi @Hazika Reshi

I hope you're well and safe!

Are the pdfs names with anything that could be matched to the row in Smartsheet?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Hazika Reshi · 06/30/23

https://community.smartsheet.com/discussion/comment/383358#Comment_383358

Hi @Andrée Starå

Thank you so much for responding. I appreciate your help.

The pdf names are in the following format: DATE_VENDOR_PO# number.pdf

For example: 20230630_ABC_PO# 2789.pdf

My sheet has a DATE column, a VENDOR name column and a column with the PO# numbers.

The pdfs are also sorted by Vendor names. So the path is Dropbox>Main folder>Vendor folder>pdfs, where the pdfs are sorted by date in the Vendor folder.

Would you be able to help?

Thanks!

jmyzk_cloudsmart_jp · 07/03/23

Hi @Hazika Reshi

Dropbox's file URL structure is as follows;

https://www.dropbox.com/s/{url_id}/{file_name}.

https://app.smartsheet.com/b/publish?EQBCT=7d8c88996990469dbe3cfc1259addbdd

Use Dropbox API to get all url_ids.

If you have API access to your Dropbox account, you will be able to get the list of url_id and file_name so that you can create a sheet with the file_name column and url_id columns with the data obtained by API calls. (*)

Then, if your Smartsheet converted Excel sheet has file_name columns, you cat get url_id using the file_name as the key. (VLOOKUP or INDEX())

To create a Dropbox URL, use a simple formula like this; (Even if you omit the /{file_name} part, the URL still works.)

="https://www.dropbox.com/s/"+[url_id]@row

(*) Example snippet to get url_id

https://gist.github.com/jmyzk/6ca64ccf5ad8531e3cea98d0fb32df58

Paul Newcome · 07/03/23

You should definitely be able to use the API to create links to your dropbox locations or even attach the PDFs directly to the appropriate rows since it looks like you have a pretty clear way of matching things up.

Sheets

Answers

Categories