TL;DR – quick generation of a Regex string inside a google sheet formula. I have leveraged other online Regex tools, but this makes it seamless to implement as a working formula. Allowed me to stay focused on the overall task and not dive into the vagaries of Regex and Array formulas.
I need a google sheet formula in Column G to extract the docID from this URL in Column R: https://docs.google.com/document/d/VCN6rGL1Ftrysyg9J8cyk/edit
The formula you can use in Column G to extract the docID from the URL in Column R is:
This uses the REGEXEXTRACT function to extract the docID from the URL, which is the string of characters between “/d/” and the next “/” in the URL. The docID will be captured in the parentheses in the regular expression, which is passed as the first argument to the REGEXEXTRACT function. The second argument is the cell containing the URL that you want to extract the docID from.
Note: This formula will extract the docID from the given URL format only. If the URL format is different this formula will not work.
Can you make this an array formula?
Yes, you can use an array formula to extract the docID from multiple URLs in a range of cells. To do this, you can use the following formula:
This uses the ARRAYFORMULA function to apply the REGEXEXTRACT function to a range of cells (R2:R) rather than just a single cell. The range of cells containing the URLs is passed as the first argument to the REGEXEXTRACT function. The second argument is the same regular expression used in the previous formula.
The editor for google apps scripts is OK for simple (single file) macros or scripts. But it falls short when you are managing more complex scripts and multiple files. That is where google clasp comes in. Google clasp is an extension for VS code that allows google apps scripts to be updated locally, with changes easily pushed to your script.google.com. It does not replace git, as it does not do version control, but it does do basic syncing well. It has its own .claspignore file like git and uses similar commands – clasp push to send code to google.com and clasp pull to get code down. But it is rather crude – there are no controls and it overwrites everything on script.google.com with each push. I assume pulls would do the same, but I have not done them and would caution against them. A simple json files clasp.json – holds the scriptID of the remote target and a path to the local “repo”. I have a dev and prob remote target and swap our clasp.json files as needed. Pushing to multiple targets seems to be possible, but I have not implemented it.
I followed this basic guide to get up and running – thank you Learn Google Spreadsheets. As this guide is geared to Mac users and I am running on WSL2 on windows, I had to do a few particular things to get clasp installed.
One clear anomaly that clasp handles is the conversion the .gs file extensions on script.google.com to .js file extensions in your VS code. I also have a remote git repo connected. The remote git repo works pretty normally, but it does have .js extensions so it cannot be hooked up directly to script.google.com. I would need another repo that connects to the gashub extension for chrome to hold a repo of .gs files. Pretty convoluted, but it works.
I found that having VS Code really facilitates refactoring. I basically treat the script.google.com as the test server environment and use the watch command (clasp push -w) to make updates quite seamless. All in all a thumbs up.
UPDATE – for those interested – here is my full workflow.