Convert Attachment URL to Links in Custom Reports

If you have requested attachments from applicants using the File Upload field on a custom narrative page, you will see that this attachment’s URL is part of a custom report when that page is included. The URL is not currently clickable because it is in the same cell with the file title and sits within parentheses. 

You can easily reformat the column so that you can click the URL for this attachment directly from the spreadsheet, making it easier to do a mass review of attachments. 

Below are directions for making this modification using Google Sheets and Excel. The first set of steps includes external resources and assumes a basic understanding of Sheets. The second and third set of steps are comprehensive and will walk you through the entire process.

• Converting Links in Google Sheets (linked resources)
• Converting Links in Google Sheets (full steps)
• Converting Links in Excel (full steps)

Converting Links in Google Sheets (linked resources)

1. Create your Report in GO Smart
2. Open the downloaded report in Google Sheets
3. Locate the column that needs to be split so the link is in its own cell
4. Add 4 new columns to the right as buffers while you do the following
5. Follow this tutorial to split the cell. I recommend splitting by typing in “(http” and then repeating the process to split by “)” – this will leave you with the title of the document in the original cell and most of the link in the second column
6. Add a new column to the right of the column with incomplete links and make sure there are no existing values in the new column
7. Follow this tutorial to add back in the HTTP prefix to all the incomplete links
8. You should now have a new column with clickable links that will open the file

Converting Links in Google Sheets (full steps)

1. Navigate to Reports > Custom Report Builder and generate a report with your desired data points being sure to include the page(s) that contain your desired attachments.
2. Download the Report and open the file in Google Sheets
3. Locate the column you need to convert to links. It will be a combination of the document title and a link in parentheses, 
   1. it will look something like this: “IRS 5013c determination letter.pdf (http://WESTAFARP.gosmart.org/applicant_uploads/142858_123456.pdf)”. Unfortunately, Sheets will not automatically recognize the HTTP portion as a link the way that Google Docs does, thus the need for these steps.
4. Add 4 new columns to the right of this column; one or two may be unused and will just be a buffer to avoid overriding other columns. They can be deleted after all steps are completed
5. Highlight the column with the file name and parenthetical link.
6. Click Data > Split Text to Columns (Or follow this tutorial to split the cell)
7. When you see the Separator tool, click the down arrow
8. Select Custom at the bottom of the options, and it will become a field in which you can type
9. Type in (HTTP – be sure to include the leading parentheses and click return or enter
10. You’ll see most of the link is now in your first new column, but it’s missing the HTTP prefix
11. Highlight this new column
12. Click Data > Split Text to Columns 
13. When you see the Separator tool, click the down arrow
14. Select Custom at the bottom of the options, and it will become a field in which you can type
15. Type in a single close parenthesis ) – this will remove the outside parentheses, and now you should see the link without HTTP and without any parenthesis
16. Add a new column to the right, select the new column, and click delete just to ensure there are no values in the new column
17. Place your cursor in the top row (or the second row if you have headers) of this new column to the right of the links that are missing the HTTP prefix
18. Continue on with step 19 or reference this tutorial to add back in the http prefix
19. Type =arrayformula(“http” & XY:XZ ) where X is the column with your cells that need the prefix, Y is the first row, and Z is the final row that have cells you want to append (so typically something like R2:R300 if R is the column with the incomplete links and you have 300 rows of data to convert)
20. The URLs should now appear as clickable links in your new column
21. Delete any superfluous buffer columns 
22. Remember that your two new columns have vital formulas looking at them, so do not delete those columns. If you don’t need to view them, instead hide them. Or alternatively, copy and paste the new URL column as values into a new column so that you can delete the columns used in the formula.

Converting Links in Excel (full steps)

1. Navigate to Reports > Custom Report Builder and generate a report with your desired data points being sure to include the page(s) that contain your desired attachments.
2. Download the Report and open the file in Excel
3. Locate the column you need to convert to links. It will be a combination of the document title and a link in parentheses, 
   1. it will look something like this:“IRS 5013c determination letter.pdf (http://WESTAFARP.gosmart.org/applicant_uploads/142858_123456.pdf)”Unfortunately, Excel will not automatically recognize the http portion as a link, thus the need for these steps.
4. Add 4 new columns to the right of this column, one or two may be unused and will just be a buffer to avoid overriding other columns. They can be deleted after all steps are completed.
5. Highlight the column with the file name and parenthetical link.
6. Click Data > Text to Columns 
7. Keep “Delimited” selected and click Next
8. Check the Other checkbox and type a colon : and click Next
9. Keep “General” selected and click Finish
10. You will now see data in your first new column beginning with “//” and your gosmart URL
11. Highlight this new column and click Data > Text To Columns
12. Keep “Delimited” selected and click Next
13. Check the Other checkbox and type a closing parentheses ) and click Next
14. Keep “General” selected and click Finish
15. This action removed the ending parentheses and has left your new links only missing the http: lead in
16. Highlight this new column with cells beginning “//” and your gosmart URL
17. Place your cursor in the top row (or the second row if you have headers) of the column to the right of the links that are missing the http prefix
18. Type = CONCATENATE(“http:”,XY) where X is the column to the left with your cells that need the prefix and Y is the first row with incomplete URLs
19. Click Return/Enter
20. Note the new cell now contains the complete URL beginning with http.
21. Highlight this new cell, move the cursor to the bottom right corner of the cell until you see a crosshair mark, and hold the mouse down while you drag this column all the way to the bottom of your last row of data
22. You should now see complete URLs in every row of this column 
23. In the next blank column, place your cursor in the top row (or second row if you have headers) 
24. Type =HYPERLINK(XY) where X is the column to the left with your cells that need to become clickable links and Y is the first row with non-clickable URLs.
25. Click Return/Enter
26. Note the new cell now contains the clickable link.
27. Highlight this new cell, move the cursor to the bottom right corner of the cell until you see a crosshair mark, and hold the mouse down while you drag this column all the way to the bottom of your last row of data
28. The URLs should now appear as clickable links in your new column
29. Delete any superfluous buffer columns 
30. Remember that your two new columns have vital formulas looking at them, so do not delete those columns. If you don’t need to view them, instead hide them.