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:

  1. See tabs with settings, their names are inside underscores.

  2. Adjust settings: change file, and folder IDs.

  3. 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

😎Other Automations?

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 ↓

⚡ Automation Samples

⚙️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_messagespaired 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:

  1. 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.

  2. 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:

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!