TSDynamicUrls (#1) — Leverage the URL Power of Google Docs, Sheets, Slides, Drawings & Forms 💥
In my previous post, I outlined the power of Google document URLs to perform “Simple, Powerful, Work” and promised to share several tools I’ve developed to aid in building them.
This post introduces the first of these tools: TSDynamicUrls — an Apps Script powered dynamic URL generator (hosted in Google Forms) which transforms Google document URLs into new resource formats.
Let’s dive in and see how you can utilize this tool in your G Suite for Business or G Suite for Education organization.
TSDynamicUrls — An Overview
As detailed in my earlier post, Google document creators can make simple modifications to shared URLs of Docs, Sheets, Slides, Drawings and Forms to transform them into entirely new resources. These resources can be distributed with document consumers to leverage their URL power.
Google document creators are assisted in maintaining single sources (i.e., the original Google documents) while at the same time possess confidence that document consumers are receiving the latest version in the desired resource formats.
But remembering all the possible URL transformations for these formats is difficult. As it turns out there are a dizzying number of options available as indicated in Diagram 1.
Google Sheets PDF URLs alone have many options as reflected in Diagram 2.
To help corral these multi-directional options, I chose Google Forms to host this version of TSDynamicUrls. They efficiently support branching and automation via Apps Script.
TSDynamicUrls — Getting Started
To Install TSDynamicUrls:
- Login to Google Drive.
- Click TSDynamicUrls Google Form. This will open the TSDynamicUrls Google Form template.
- Click the USE TEMPLATE button in the top right.
- Wait for the form to be copied to your Google Drive.
To Enable TSDynamicUrls:
- Locate and open the newly copied TSDynamicUrls Google Form in your Google Drive.
- Wait for the form to fully load. The form add-ons menu indicated by a puzzle icon will appear at the top when the form is loaded.
- When the form add-ons menu appears, select TSDynamicUrls → Enable form from this menu.
- The first time the menu is accessed it will prompt for authorization. Complete the authorization flow by following the Google authorization prompts. This is a one-time authorization.
- A confirmation message will appear when the configuration process is complete. Dismiss the confirmation.
TSDynamicUrls is now enabled! Share the form preview with members of your organization and empower document creators to leverage the URL power of Google documents.
TIP: Embed TSDynamicUrls Google Form in a Google Site so it’s easily accessible within your organization.
Important Usage Notes:
> The TSDynamicUrls Google Form automatically collects email addresses to send transformed URLs to form submitters. This requires the form to run inside a G Suite for Business or G Suite for Education organization. This form will not work in a regular Gmail account.
> TSDynamicUrls employs Apps Script and runs under the G Suite user account who installs it. Apps Script is subject to daily quotas so see the Frequently Asked Questions below for more on these quotas along with best practices for deployment.
> Check the Frequently Asked Questions for more on what changes can and can’t be made to the TSDynamicUrls form.
Once the TSDynamicUrls form is installed and enabled, Google document creators within your organization can take advantage of it by following these simple steps reflected in the diagram above:
- Create a Google document (Docs, Sheets, Slides, Drawings & Forms).
- Appropriately share the Google document based on audience.
- Copy the Google document URL (from the document sharing settings or from the open document browser address bar).
- Submit a document URL transformation request to the TSDynamicUrls form using the copied URL.
- An email will be sent to the form submitter with the newly transformed URL.
TIP: Google document consumers within your G Suite organization can also use TSDynamicUrls to leverage the URL power of document links shared with them.
Following are key items to keep in mind when using TSDynamicUrls:
Google Sheets URL transforms for CSV (comma-separated values), TSV (tab-separated values) and individual sheet PDFs require a sheet ID to be included in the submitted URL. To get this sheet ID:
- Open the Google Sheet with URL to be transformed
- Navigate to the desired sheet
- Copy the URL from the document browser address bar
The URL should contain a sheet ID as highlighted below.
https://docs.google.com/spreadsheets/d/1TxINLwjEkB8QnsaGgKpzvV_VjtFYHVmhPqdB5hFjC3E/edit#gid=929929678Google Slides URL transform for individual slide PNG images requires a slide ID to be included in the submitted URL. To get this slide ID:
- Open the Google Slides with URL to be transformed
- Navigate to the desired slide
- Copy the URL from the document browser address bar
The URL should contain a slide ID as highlighted below.
https://docs.google.com/presentation/d/1S5pu1SC1VmhDYxJppLVFTCBqaLftiedCKKk43yYpHgo/edit#slide=id.gc6fa3c898_0_5Here are important items when using Google Sheets PDF:
> Repeat Frozen Rows — frozen rows must be specified on the sheets to be transformed
> Repeat Frozen Columns — frozen columns must be specified on the sheets to be transformed
> Custom Margins — all four margins must be specified
> Named Range— the named range must exist on the sheet to be transformed or the generated URL will be invalid
> Custom Range — start row and start column must be less than or equal to the end row and end column, respectively, or the PDF URL will default to the entire sheet
A CAUTION WHEN SHARING GOOGLE FORMS:
Most Google document types allow file sharing with view only access. Google Forms by default are shared with edit only access.
Edit only access can be problematic when sharing a Google Form copy or template link with users (especially outside your G Suite organization). Anyone possessing the shared link can turn it into an edit link and modify your Google Form.
Follow this guide to share your Google Forms with view only access.
TSDynamicUrls — Frequently Asked Questions
I don’t like the TSDynamicUrls Google Form theme. Can I change it?
Yes. Here’s a list of the things which CAN be changed on the TSDynamicUrls form:
- Form color, theme & header image
- Form title & description
- Form confirmation message
… and here’s a list of the things which SHOULD NOT BE CHANGED for the TSDynamicUrls form to work properly:
- Form collecting email addresses (needed to send form response email)
- Form section names, order, locations, branching locations
- Form question types, names, locations, branching locations, validations
Anything else I need to be aware of when using TSDynamicUrls?
TSDynamicUrls is powered by Apps Script which is subject to daily quotas based upon the type of G Suite account hosting the script. For more see Quotas for Google Services.
Because TSDynamicUrls runs from the G Suite account of the person who installs it and uses their Apps Script quota, it’s best practice to install and configure TSDynmicUrls in a separate, dedicated account inside your G Suite organization. Consult with your domain administrator.
Someone submitted a response to the TSDynamicUrls form I set up in my organization but did not receive an email. What should I do?
If TSDynamicUrls is not working properly, try the following steps:
> Check the log files in the G Suite account running TSDynamicUrls for errors
- Go to script.google.com
- Find the TSDynamicUrls script in the script list
- Click the more icon to the right of the script name
- Select Failed Executions
- Look for error messages
> Run the TSDynamicUrls > Enable Form option as described above and re-enable and authorize the script in the event that the form submit configuration trigger was removed
> Ensure that you’ve not exceeded any Apps Script Quotas in the G Suite account hosting TSDynamicUrls
> Ensure you are running TSDynamicUrls in a G Suite for Business or G Suite for Education organization and not in a regular Gmail account
The generated URL seems to be invalid. What should I do?
> Ensure the original Google document exists for the URL being used to generate the dynamic URL
> Ensure the original Google document is shared correctly with the document consumers trying to reach it
> If using the Named Range option on a Google Sheets PDF, check that the named range exists on the sheet to be transformed or the generated URL will be invalid
Where can I find this project?
See the TSDynamicUrls project on Github.
TSDynamicUrls is missing some important URL format transforms. How do I report these?
Please leave a comment on this post or open an issue here.
Need help automating processes inside your G Suite organization? Get in touch
Follow me on Twitter, Medium & Github.
