Because Microsoft Word and Excel files can go in and out of Google Docs and Sheets (mostly) well, you can now extract lines from Google Docs into Google Sheets. Generate HTML from Google 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.
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.
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