Automate
.online
⚡Automation Samples
With this sample Google Sheet & Script, you can automate your daily tasks.
Please make your own copy to try it:
Quique start:
See tabs with settings, their names are inside underscores.
Adjust settings: change file, and folder IDs.
Use the custom menu to launch the script. First call: Authorize the script.
🍏Automations for Sheets
Copy Ranges & Values
Clear Ranges
Delete Rows
➕➖Editors for Protections
🍋Automations for Drive
Copy Files
Create Folders
Create Docs by Template
⚙️How it works
Automation functions have 2 parts: defining things and looping things:
function ⚡⚡⚡() {
var 👜 = [🍓,🥝,🍊,🍌 ];
for (each 🟠 in 👜) {
🔪 (🟠);
}
}
Each useful function deals with one line of settings: we cut by one fruit at a time.
The code uses settings from Sheets.
One line of settings equals one loop of automation. Here's a sample Sheet containing settings for clearing ranges ↓
↑ Cells from the first row are holding special keys.
Please see keys for this automation: sheets_name and range_a1 . They help the script to form settings:
sets = {
sheets_name: "Sample3",
range_a1: "E4:E17"
}
You may notice other keys on the left side of a setting sheet; here they are:
delimiter — any char for splitting things in settings, I usually use |
todos — this is the way we may stop this line execution from a sheet. To run this line, use 1. To make 2 or more checks, use delimited numbers: 1|1, 1|0. The script will execute the line if all todos = 1.
exit_messages — paired with todos, and also delimited: message1|message2. They specify what message to show if one of the todos is 0.
log_file_id — specify if you want to write logs to another spreadsheet. Leave blank to use the current file.
log_sheet_name — sheet name for logs. May leave blank if logs are not needed.
success_message — the message returned by the function in case it was finished successfully.
If you specify the name of a sheet for logs (log_sheet_name), the script will write logs there.
Here's a Sheet with sample logs ↓
It contains columns populated by Script:
timestamp — time the function was executed.
user — who executed the code
json_log — information about settings and the result
You may also notice column [D] with Success Message. It is populated by the formula from the json_log column (see how).
Each automation has its own custom success message, exit message, and other preset stuff, which is optional. All this and more is passed to the executive function. While the function runs, it sets new values to the resulting JSON.
Sample JSON logs look like this ↓
↑ Each function will run through the decorator. Decorator deals with Logs. It fills function name, and execution time.
Automation samples solve 2 basic problems:
Settings. The script supports both: holding settings in Sheets and using external settings. All executive functions have an object parameter, and we may use JSON text from an external source. The code also contains methods to easily read data from Sheets and convert it into a list of objects to execute each automation.
Logs. The script writes logs into a Sheet. Logs are passed as JSON + Timestamp + User. Thus we'll have all we need to explore the results of our scripting work. If we need some specific fields from logs, we'll get them with the help of the `REGEXEXTRACT` function.
Sources:
Sample File: https://docs.google.com/spreadsheets/d/1HwUaZk86BtrPdQ1RYILwTcRwJUUClgqtAPEpMAsX0y8
Bound Script: https://script.google.com/u/0/home/projects/1Xzr2byUscEaiabij0l0lr4Vpe5NTL63c-qDvHofATyNPUhjpq190Wv2e/edit
Folder with samples: https://drive.google.com/drive/folders/1TcbXdE9C7B3L8M3fdm4vh8ItpyZnCJcG
If you've read this far, I believe, you are my colleague — Google Apps Script developer. I'd be glad to hear your thoughts.
Please see my contacts below.
Any ideas are welcoming!