Jobs

Copy sample

About

Usage

Jobs is a Goole Spreadsheet with the script for automating tasks.

• Installation
• About
• File structure
• Settings
• Script
• Full list of functions ☠️

Installation

The simplest way is to create your copy of Jobs.

About Jobs

Here's sample workflow for Jobs:

1. Copy the range (from source1) →
2. Copy the range (from source2) →
3. Combine the data (from steps 1, 2) →
4. Filter the data (from step 3) → 
5. Paste it (somewhere).

The advantage of using Jobs is to save time on repeated tasks. The basic functions are:

clearRangeContents_
rememberValues_
logValues_
copyByTemplate_
filterByColumn_
writeValues_

New functions will appear in Jobs as native bound functions, or will be added as libraries (see more about libraries in Google Sheets).

Jobs has a flexible system of selecting ranges in Google Sheets. A range is the smallest part of Spreasheet object model. Having a range, the script will find all parent objects from it if needed.

Jobs structure

Jobs is a Google Spreadhsheet file. The file contains of 3 sheets:

Settings Jobs

All settings are in the sheet _Jobs_:

↑ Each line of settings represents one task for Jobs.

The table below gives basic info about settings.

Notes:

• Some settings may be left blank. In this case the script will use
  default values (see the coulum |Default|).
  • Some settings are the
    same for all tasks (see the basic type in column |Type|).

Task Id. The unique ID of a task. Use numbers 1, 2, 3. Textual format of ids is also possible: 1a, 1b, my task. CAUTION: use unique IDs only! 2 tasks with the same ID will run simultanously, which may cause errors.

File Id. Copy from file URL:

Or get file id from the script using File.GetId() method. Default is the file where whe script was executed: SpreadsheetApp.getActive()

Sheet Name. Sheet names are unique and being used instead of ids for user convenience Default is the left-most sheet in a file: File.getSheets[0]

Range-A1. A range address in A1-Notation: A1, A1:C5. Default is range with data: sheet.getDataRange()

Range Type. Select between options: range only (default), range and rows behind, range up to the end of sheet, first free row

Operation. The name of a function to run. You may use Jobs functions or develop new functions for your needs.

Option1, Option2, Option3. Contains any text depending on the script settings.

Tag. The name of a group of tasks. Use it to easily run multiple tasks with one line of code: run_JOBS_('Clear Ranges'); where Clear Ranges is your custom tag.

Jobs Script

To see the script, in Jobs file go to menu: Tools > Script Editor.

The basic usage is to run this code:

// Change tag /tags and run the function
// To run more then 1 tag, use · as a delimeter: Clear Ranges·Log Values
function test_Jobs()
{
  // Test Tags:
  //   --------------------------------------------------------------------------
  //   Clear Ranges     = clear values from selected 
  //   Log Values       = run the code and open the log: [Ctrl]+[Enter] 
  //   Copy Report      = create a copy of the given report-template
  //   Fill Report      = fill the report with the portion of filtered data
  //   --------------------------------------------------------------------------
  run_JOBS_('Clear Ranges');    
}

The core of Jobs functionality is using data. Data is stored in Google Sheets as 2-d arrays:

[
 [value], [value],
 [value], [value],
 [value], [value]
]

↑ This sample data contains 2 columns and 3 rows.

Here's how the script treats data:

Jobs Functions

Full list of Jobs functions

rememberValues_

logValues_

copyByTemplate_

Creates a copy of Spreadsheets with settings.

filterByColumn_

writeValues_

createDataValidation_

sendGmail_

hideRows_

createPDF_

deleteRows_

setColumnFilterCriteria_

copyRange_

copyRangeContents_

runPureAlaSql_

Uses this alasqlgs library.

runCol1AlaSql_

Uses this alasqlgs library.

AlaSqlMore

Note: in Ala-Sql functions are used without trailing undercope date2num_ → date2num

data2Num

date2num_ = function(date)
// usage:
select data2Num(Col1) from ?
// converts date to a number in format YYYMMDD
date '2019-06-12' → 20190612

dayaddnum

dayaddnum_ = function(numdate, num)
// usage:
select dayaddnum(Col1, -1) from ?
// adds days to the initial date-number
dayaddnum(20190612, -1) → 20190611

datediffnum

datediffnum_ =  function(numdate1, numdate2)
// usage:
select datediffnum(Col1, Col2) from ?
// gets difference in days between 2 date-numbers
datediffnum(20190612, 20190611) → 1

num2date

num2date_ = function(numdate)
// usage:
select num2date(Col1) from ?
// converts date-number to date
num2date(20190612) → date '2019-06-12'

More info about Jobs:

1. How to use Jobs
2. General info
3. How I store variables — about the file Const.gs

Archive samples (will migrate to Jobs):

Files & Folders

FolderFiles
Create a list of all files from selected folders.

FileCopier
Copy files to a selected folder.

FolderMaker
Create new folders.

Sheets & Ranges

RangeCopier
Copy ranges to custom ranges, sheets, and files.

SheetsCopier
Copy multiple sheets. Remain formulas, named ranges, protections and sheet names.

SheetsDeleter
Delete multiple sheets.

TeplatesCopier
Create custom reports. Copy own template, rename it, set it to show different data.

Other

Emailer
Send custom emails

Lisense