Automate

.online

⚡Automation Samples

With this sample Google Sheet & Script, you can automate your daily tasks.

Install


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:

 👉🏽 During the first call: authorize the script and run it again.

The menu splits scripts by the field of automation: scripts related to Sheets, Drive, Mail, and Forms.

🍏Automations for Sheets

🍋Automations for Drive

😎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 ↓

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

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:

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!