Convert Google Doc hyperlink in a cell to an attachment

mashermags

We have some sheets containing very large numbers of hyperlinks to Google Documents. The links are in a single column, with one hyperlink in each row.  This procedure has been followed for several years, rather than using the attachments facility, for internal security reasons which no longer apply.

Our organisation is now moving away from Google in favour of Office 365, which means that whilst all the documents will be migrated to OneDrive and converted to Word format, the URLs will no longer work. One possible solution which has occurred to me is to run some automated process which would use the hyperlink in a cell to extract the Google Doc and convert it to an attachment (in say DOCX or PDF format) to be stored against that row.

A long shot I know, but can anyone think of a way to achieve this? An alternative will be to retain a Google account purely to store the legacy Google docs.

Andrée Starå

Hi,

Fortunately, it's possible. 

I'd recommend using Find/Replace and the method below to create the URLs to save as PDF/DOCX or what's needed.

PDF Link

Share a direct download of a PDF version of your document

→ Google Docs & Sheets: Replace /edit with /export?format=pdf

→ Google Slides & Drawings: Replace /edit with /export/pdf

Have the web browser download a PDF version of your document with a PDF link. Instead of displaying the document in Google's viewer or app, a PDF is automatically downloaded when the link is clicked.

• Since a PDF is a universal format, a Google account is not required to download and not necessary to open the file.

• Direct downloads are not limited to PDF. Other file types also work. Instead of using pdf in URL, try png, jpg, pptx, xlsx, docx, html, or txt.

• Source

Would that work?

Hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

mashermags

Many thanks for the reply, and that’s an interesting way to change the way a Google doc link works, by making the link download a PDF instead of opening the document directly.

But as I understand it, it’s still a URL link to a document in Google Drive, and all our documents will be moving to MS OneDrive, where they will have completely new URLs.  So I either need to pull each Google doc out of Google Drive and into the associated Smartsheet row as an attachment, or to somehow replace/translate/redirect each existing URL to its new location in MS OneDrive (and I’m told that the migration process won’t make that possible).

Please let me know if I’ve misunderstood your suggestion – and have a great weekend yourself!

Andrée Starå

Happy to help!

My thought was that you could download each file by clicking on the link and then re-add it to the new structure.

There might be a possibility to automate it. How many files/links are there?

mashermags

Thanks again Andrée. Unfortunately the team concerned has created approx 4000 of these links over a period of about 6 years, so a manual process to replace the links is not realistic There is one sheet for each year, and each row is linked to a detailed job description document, containing mostly unstructured notes and photographs etc. The information is not going to be lost, because the documents are stored in a logical folder structure within Google Drive, which will be replicated in the migration to the new MS OneDrive environment. So it will still be possible to locate the required information when necessary, just much less user-friendly.  It's basically historic records of work done for reference purposes, so not critical to the operation of the team. I think we may just have to live with this issue, unless there is some kind of macro-type routine which could pull out the linked document on each row and save it as an attachment.

The whole issue only arises because some of the documents contain personal information and contact details for clients etc., and our security team wanted to lock that down with strict access controls in Google Drive, rather than trust it to what was seen as a fledgling cloud-based service (Smartsheet) at that time.  Going forward, I think I can probably persuade them to alter that policy, and accept the idea of using file attachments in Smartsheet. After all, if we ever switch away from OneDrive, the same problem will arise again.

Please don't devote a whole lot of time to this, but if you can envisage a simple way of solving the problem, I'd love to hear about it!

 

 

Andrée Starå

Ok.

I'll get back to the post if I come to think about some other way to solve it.