Importer-v.02

Platform: #Google-Spreadsheets

Code: #Google-Apps-Script

Purpose: Imports data between files with conditions.

Landing page: https://sheetswithmaxmakhrov.wordpress.com/2018/04/10/importer/

Sample file: https://docs.google.com/spreadsheets/d/1bslkvNqLsp3_W1KeNXT4QPDbSxVu_DOFWYErpbccuiw/copy

Sample folder https://drive.google.com/drive/folders/1RsMyx_aQU2RFq2pPvhlmVNRpIJMrqaHO

Set connections between sheets. The script will do the rest.

Targets

Fill targets on sheet \Targets/.

Targets are tables where to paste the info:

↑ Each row represents 1 target on sheet \Targets/

The script needs to know some basic info about your tables. Fill in the info about your targets: file ids, sheet names, and first rows where to paste the info. Give a unique id to each of your targets.

Add SQL text to a column called "Query Target". The simplest query is:

SELECT * FROM ?

It will select all records from the table.

Set "Cleat old data" to 1 if you want to clear the previous data from the target sheet.

Sources

Fill sources on sheet \Sources/.

Link each source with its target by "Task Id":

↑ Each row represents 1 connection "Source→Target".

↑ Connections are made by unique Task Ids. One target may contain multiple sources.

Fill in the coordinates: File Id, Sheet name and First row with data. Fill "Query Source" with a proper SQL request.

 

First launch

After you finish with settings:

1. Launch import: [Admin] > [Make import now]. Do it any time you need to refresh your data.
2. Run import hourly: [Admin] > [Install] > [Set 1 Hour Trigger].  Do it once and the trigger will run constantly.

If you want to add more connections, set them in new lines, and the Importer will adjust new settings automatically.

What the Importer will do

The Importer works in 3 steps: Sources → Target → Import.

Step 1, Sources.  See the source table => modify it with SQL. "Query source" is applied in the first step.

Step 2, Target. Combine one or more source tables into one target => use SQL to combine multiple sources.  "Query target" from sheet \Tatget/ is applied in the second step.

Step 3, Import. Delete existing data if needed => paste the new data.  Note: the data is deleted from the columns of import only.