With this sample Google Sheet & Script, you can automate your daily tasks.
Copy⚡Automation Samples Spreadsheets to your Drive.
See tabs with settings
Launch the script
Each automation has a sheet with settings. These are special sheets, their names are inside underscores: _name_.
I've added protection to these sheets so you see the lock near their names:
This is a sample sheet with settings:
Each row here represents one task. Each column represents one parameter for the script to accomplish the task.
All sheets with settings have a similar structure. Some parameters are common. The other parameters are specific to the task: sheet names, row numbers, etc. You may change each parameter and test the automation, running the script.
Use the custom menu to run the script. This menu will appear on the top right corner of your menu panel in Sheets. Please authorize the script, you'll need to do this only once:
The menu splits scripts by the field of automation: scripts related to Sheets, Drive, Mail, and Forms.
🍏Automations for Sheets
🍋Automations for Drive
Automation samples is created for lazy developers like me. It allows us to easily create new automation based on the existing code.
I plan to add new features, here's my current todo-list ↓
⚙️How it works
Automation functions have 2 parts: defining things and looping things:
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:
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.
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!