If you liked Microsoft’s VB script for Word, Excel, you’ll love Google App Script for Google Docs and Sheets
This can be a common situation needing automation.
NOTE: Content here are my personal opinions, and not intended to represent any employer (past or present). “PROTIP:” here highlight information I haven’t seen elsewhere on the internet because it is hard-won, little-know but significant facts based on my personal research and experience.
Ben Collin’s $198 for 25 examples over 70 videos on Google Apps Script
My project: sync between Google Doc and Google Sheet
We can progress in stages:
First of all, this can be done old school after export to Microsoft Word and Excel files, then use VBA. But the corporate mandate is to use Google.
I was thinking this can be best done using Python reaching into Google Worspaces, using the Google Sheet API or writing out to CSV file.
The end objective is that a change can be made in the Doc and be reflected in the Sheet. There are several sheets: one for text tagged as a QUESTION, another for TODO, etc. The sync is necessary because the sheet has additional columns associated with each text line. For example, the QUESTION sheet data is associated with a flags for applicable to each persona (job role). The sheet is used to generate docs of questions applicable only to each persona.
Sequentially scan through (line by line) a Google Doc for lines tagged with “QUESTION:” and paste that line in a Google Sheet.
Create in new Google Sheet
Have button in google Doc & sheet which invokes the script to run.
Add a sequential number to each QUESTION found and update the Doc so there is a unique reference into the Sheet.
Add or Update Question Number in Doc: from “QUESTION: text” to “QUESTION 33: text”. For now, Ignore anything between QUESTION and :.
Add a sortable date/time stamp column in Sheet for each item added or updated.
Format of the date/time stamp: 22-09-20T24:59:59Z (for Zulu/GMT local time)
Include in the Sheet a _Origin column a link back to where the item was found in the Doc.
This means that you’ll be keeping track of headings as you progress sequentially through the file.
Example: “Heading 1” etc. captured as you progress through the Doc.
In origin, add a URL link to the Heading text.
Add a sortable date/time stamp to the existing item in the Sheet.
Here’s the tricky (but cool) part - syncing between Doc and Sheet.
From here, we will be updating the Sheet rather than creating one.
We are adding IDs in the Doc and sheet (such as SEC-02-1) which provides a key to those extra columns in the sheet. Based on that code, changes in Question text in the Doc should update the sheet.
Update the sortable date/time stamp to each line changed in the Sheet.
If there is a difference, please add a text in the _Note field in the Sheet.
NOTE: This is what the file looks like after processing. Initially, there would be no number after the QUESTION. On the first pass, create a new sheet. In the Doc insert a sequential number between QUESTION and : (I prefer to have a space before and after the colon : ). Let’s call that Question ID or QID. On the first pass, remove any text between QUESTION and : then replace it with a number.
On subsequent runs, open the Sheet to update. Do not update the Doc file. We will need to change the QID in the Doc, such as BX-22 at the bottom.
Generate a new document for each additional column (one additional column for each user role).
In each document for a particular role, only print the question text which has been flagged “Yes”/True in the column for that role.
Additional tags, such as TODO, PROTIP, etc.
https://www.google.com/script/start/ Google Apps Script home page
Google announced that it will discontinue its Apps Script UI Service on July 15, 2019.
https://edu.gcfglobal.org/en/topics/googleapps/ Tutorials on Gmail, Keep, Docs, Sheets, Drive, Slides, Maps, Forms
https://www.darkreading.com/cloud/google-apps-script-vulnerability-exposes-saas-to-url-based-threats Jan 4, 2018
https://pulse.appsscript.info/ by Martin Hawksey
https://developers.google.com/community/experts/directory Directory of Experts worldwide
https://www.amazon.com/Beginners-Guide-Google-Apps-Script/dp/B08C94RJMC Beginner’s Guide to Google Apps Script 1 - Sheets (Step-By-Step Guides to Google Apps Script) by Barrie Roberts
https://learning.oreilly.com/library/view/google-apps-script/9781491946176/ch01.html Google Apps Script, 2nd Edition by James Ferreira
https://www.youtube.com/watch?v=70F3RlazGMY Google Apps Script - Get and Set Values on Google Sheets
https://codelabs.developers.google.com/codelabs/apps-script-intro#0 Feb 11, 2022 by Wesley Chun (@wescpy)
More on MacOS
This is one of a series on MacOS:
- MacOS Setup step-by-step, with automation
- MacOS Hardware and accessories
- MacOS dotfiles for System Preferences setup automation
- MacOS Boot-up
- MacOS Keyboard tricks
- MacOS Terminal Tips and Tricks
- Text editors and IDEs on MacOS
- MacOS Xcode.app and CommandTools (gcc)
- MacOS Command-line utilities
- Applications on MacOS
- 1password on MacOS
- Manage Disk Space on MacOS
- Screen capture on MacOS
- Windows on Apple MacOS
- Packer create Vagrant Windows image
- Python on MacOS
- Maven on MacOS
- Ruby on MacOS
- Node on MacOS installation
- Java on MacOS
- Scala ecosystem