Cascade Dependant drop-down lists

Hello!

One of the most needed features of tables is creating dependent drop-down lists, like these:

cdl

List 1 is the main for list 2. List 2 is the main for lists 3 and 4. I’ve already supposed one possible solution. And here’s why I suggest you this one, too.

Problems solved:

  1. Limit on 500 items for the list is not the case now: cascade lists are based on a range, not an array.
  2. The speed of the script is improved.
  3. Settings of lists are easier: the code of settings is created by the formula.

The main challenge was: speed. Here’re 2 versions of the same script in comparison:

V0: reading data from the sheet. 
V1: reading data from script memory

speed-up

The result: 4 times faster!

Solution: 2 files:

  1. Main: create the reference and set the script (copy sample file and script)
  2. Work file: import the reference, use drop-down lists  (copy sample file and script).

Install Cascade lists

Create a library:

Main File 
    Menu
       Tools > Script Editor 
               (in new tab)...
                    Menu 
                       File > Manage Versions > Save new version

Save library key:

Main File 
    Menu
       Tools > Script Editor 
               (in new tab)...
                    Menu 
                        File > Project properties > Copy Project key

You’ll get a key like this:  M7_AcDUdXsYoZmAeXcXharSwhNvePg-OV

Install the library:

Work File 
    Menu
       Tools > Script Editor 
               (in new tab)...
                    Menu 
                        Resources > Libraries > Paste Project key

cddl_library+install

Set Cascade lists

The script of the work file has settings in it. All these settings may be created on the sheet “Script” of the main file. Sample settings for 1 dependency are as follows:

// [1];
obj_dv.sheets = ['Expences'];
obj_dv.dataRangeA1 = "'Imp_Database'!C4:K14";
obj_dv.rowStart = 3;
obj_dv.colStart = 2;
obj_dv.colEnd = 6;
obj_dv.sheetRefName = 'Imp_Database';
obj_dv.refColStart = 3;
obj_dv.refRowStart = 4;
obj_dv.refCols = 9;
obj_dv.refRows = 11;
var res2 = runValidation2d_(e, obj_dv);
Logger.log(res2);

You set only yellow cells:

cdlsets

When you enter the yellow cells, copy the generated code and paste it to the script of the Work file.


That’s the minimum info needed to install the script. Please post your questions on the comments. I’ll be back to add more details to this post.

Thank you!

Generate me, Jobs

How to create 30+ Google Sheet files with dummy data using Google Apps Script.

If you need 30 or more files with dummy data, with different numbers of rows, please see this solution. Think of your own set of files for tests, like this:

  1. Import a big amount of sheets with IMPORTRANGE formula
  2. Import a big amount of sheets with Google-Apps-Script
  3. See what method gives the best performance.

If you want to create your own dummy files, please follow these 3 steps:

Step 1. Copy the file ↓

Generate me, Jobs (copy)

Step 2. Fill the yellow cells ↓

ag1

Note: if you need more than 30 files, please launch the script by parties of 30 files.

Step 3. Launch the script ↓

ag2

The result:

ag3

Each dummy file has the same structure:ag4

It has a number of rows between upper and lower bounds (you set them). In my case, the file generated 400 rows, the number between 100 and 500.

It has an Id of each row, column Key.

It has some random numeric values.

It has also a column with dates, so you could test dates too.

It has the link and the name of the file in case you’ll need to check the source file.

The data of dummy files is recalculated by the formula.


Credits:

 

SheetSpam. My new video series

You’ll soon see a lot of new super-short videos on my YouTube Google-Spreadsheets blog. I called them SheetSpam for these reasons:

  1. My videos are short (less than a minute)
  2. Each video provides a single idea.
  3. Videos are silent: they have no voice‐over.
  4. You’ll be able to see about 25 new videos per month. This is spam =)

The goal is to popularize Google Sheets and share ideas. You’ll learn special functions and how powerful Google Sheets can be. There are a lot more that I want to tell than I can. This is a reason for SheetSpam. Each video will show:

The possibilities of Google Sheets are amazing!

SheetSpam videos are not so good as educational micro-video. I do not care about timing, my only rule is to make the video length less than a minute. I don’t spare time on talking and editing voice. I don’t spare time on the description below each video. I have an opportunity to express myself, and hopefully to show everything I wanted to show.

Less time on talking, more time on ideas.

I hope you’ll like the format. This is an experiment for me, it was risky to produce them before testing and seeing the reaction.

The first party of SheetSpam came to the channel on the 12 September, 19.

Join Sheets, Jobs

This article is an answer to the comment by Luis Paliotes:

Hello

I was wondering, If it’s possible to build a Google Script that performs the same action as the following query:

=query({IMPORTRANGE("SpreadsheetKey1","SheetName!A2:M");
        IMPORTRANGE("SpreadsheetKey2","SheetName!A2:M")},
  "SELECT Col1,Col6,Col4,Col5,Col8 WHERE Col5='Not Treated'")

The problem I’m facing is that I am using this Query to connect 16 different Spreadsheets, and this doesn’t work on a robust way as due to the amount of data google turns it on and of.

My answer will relate to Jobs and Ala-Sql.

I’ve created a test file with 5 different sheets to join:

js1

The problem: how to join data from 5 sheets with a condition, and put the resulting array into sheet Master.

Here are the final Jobs settings:

js2

Please learn more about Jobs if you’re not familiar with it.

The data sample from sheet S1:

js3

The resulting data in the sheet Master:

js4

Notes about the execution:

Step 1. Get the data from sheets

Fill the task id, file id, and other elements to remember initial ranges:

js5

Note the names from column Option1: it is the names of holders. Google Script will remember the data and put it into an object. The holder serves as a key to this object. You’ll need to set individual names for each piece of data in Option1.

Step 2. Join each piece of data into a single array

The query is:

select * from ? where Col1 != '' 
union all corresponding 
select * from ? where Col1 != '' 
union all corresponding 
select * from ? where Col1 != '' 
union all corresponding 
select * from ? where Col1 != '' 
union all corresponding 
select * from ? where Col1 != ''

js6

This technique is illustrated in Ala-Sql-Sheets Samples here.

Note the Option1: it is the ​ string s1~s2~s3~s4~s5 matching all 5 holders from step 1.

Step 3. Filter the result

Use SQL-query like this:

select * from ? where Col5 = 'Not Treated'

I passed the result into the holder filtered:

js7

Step 4. Refresh the result

js8

Note that the formula refreshes the result automatically. I’ve added the task to clear old values before the insertion. This will ensure that the script acts as a formula.


 

That’s it!

You may copy and test:

Happy coding!

 

 

 

Manager, Accountant, and Google Sheets

The case. Manager and Accountant

Here’s the Manager:

photo-1485712207830-8a665e701494

Photo by Daria Nepriakhina on Unsplash

She uses the phone to share photos on Facebook edit her Google Sheet with a purchases report.

Here’s the Accountant:

photo-1461988625982-7e46a099bf4f

Photo by Crew on Unsplash

He’s chatting with his girlfriend editing the main purchases report in his Google Spreadsheet.

The Manager and the Accountant use different Google Spreadsheet files. Their sheets share the same information: products, dates, prices, checks. They still don’t use the same file. There are some reasons to use different files. Differences are as follows:

  1. The manager has a small list of items, being purchased this week. The manager sees only her orders. She has a limited number of columns to see and change on the screen. The manager needs a tool to quickly change data about her items. She uses a phone, not a computer.
  2. The accountant has a full list of items. The accountant sees orders of all 5 managers and also other stuff in his Spreadsheet. He has access to all the columns and calculations. The accountant needs to see the fresh info from managers. He uses the computer to deal with all this stuff.

The problem. How to develop the connection between the Manager and the Accountant in Google Sheets

The accountant owns the main table. Will call it Main.
The manager owns the partial table. Will call it Part.

Imagine more than one manager in this system. Each manager owns her own file. This means we need to establish multiple connections:

One Main table may be connected with multiple Parts.

The real challenge is: how to remain the system up to date. How to connect Parts to Main in a way that both: the manager and the accountant are happy. Do we need to copy the data manually, use formulas, or develop the script?

The solution. Use Jobs to create connections between sheets. Make the Manager and the Accountant happy

The solution uses Jobs. If you’re not familiar with Jobs, think of it as a script full of preset standard tasks. Copy Jobs and try it in action. Each line of settings in Jobs is a single task: remember values, modify values, paste values.

jobs-lines

Jobs will accomplish all the scripting part. Let the program dealing with all the programming for you.

You’ll need to create a scenario or an algorithm. The algorithm is a list of tasks for Jobs, that you set in a tabular form (see the picture above). Here’s how it looks like:

  1. Task #1. Update Main for the Accountant
    1. Remember Part1
    2. Remember Part2
    3. Join Part1 + Part2, use a query: select * from ? where Col1 <> ''
      union all
      corresponding select * from ? where Col1 <> ''
       . Will produce the new data, call it Parts. Note: Jobs uses Ala-SQL to implement SQL requests.
    4. Join Main with Parts, use a query: select Main.Col1, coalesce(Parts.Col2, Main.Col2) label, Main.Col3 date, Main.Col4 mark from ? Main outer join ? Parts on Main.Col1 = Parts.Col1. Will produce the new data, call it “New-Main”.
    5. Replace the date from the Accountant’s table with the “New-Main”. Done!
  2. Task #2. Update Part for the Manager

…and so on. I’ll describe this method in detail and provide sample files later. Please star this post and write the comment if you’re interested in this topic, to let me continue the article.

Thanks, and see you!

Query me with SQL, Jobs

This article is related to Jobs.

Good news! I’ve added the new functions to Jobs:

runPureAlaSql_
runCol1AlaSql_

You now may query your data with SQL-like language:

SELECT 
  data1.Col1 col000, 
  data1.Col2 col001, 
  data1.Col3 col002, 
  data1.Col1 col010, 
  data2.Col2 col011 
FROM ? AS data1 JOIN ? AS data2 
  ON data1.Col1 = data2.Col1 
  order by col002 desc

Here’s the full list of functions available:

https://github.com/Max-Makhrov/sheetjobs#Jobs-Functions

Cheers!

Upload image from your computer to Google Drive using HTML-form & Show image via URL (IMAGE function) in your Spreadsheet

Problem

The user needs to show pictures: list products or see product design on the screen. The new Google Sheets feature — insert an image into a cell — works fine. The drawback of this method is that an image is actually saved into the file’s body:

  • you can’t copy this image to the other file without copying the entire sheet.
  • you risk slowing the performance of the file containing lots of images.

Solution

Save image on Google Drive and show it with the help of IMAGE formula.

I’ve managed to create a sample script that opens the HTML-form, showing images from your computer, so you could choose one and load it into Drive, and show it in a cell.

Please see the code here.

The whole trick works from Google-Apps-Script. This gives us new possibilities on user interactions. See how it does:

One cell is used to store the IMAGE formula.

img_1

The event or button in a custom menu launches the script.

img_2

The user is prompted to select an image.

img_3

The image loading is shown in an HTML-form.

img_4

When the user submits the form, the image is shown in a sheet.

img_5

The actual image is saved to the Drive folder.

img_6

 

 

 

 

 

 

Google Sheets and Regular Expressions

Please meet my personal list of regex tricks:

make a copy and see the formulas

I love using them. It worth learning regular expressions because of their power. One short smart expression may substitute a hundred lines of code.  It is my timesaver.

You may join the game! Share your favorite tricks with regular expressions. Post them in the comments, and I’ll fill my list with new knowledge. Wise men say:

There is no knowledge that is not power

and

May the force be with you!

Photo by Ian Stauffer on Unsplash

Photo by Ian Stauffer on Unsplash.

Made Jobs smarter

I’ve just made Jobs smarter.

jobs_smart

Well… Jobs is still a quite stupid robot. But it now understands new functions:

copyRangeContents_
copyRange_

These are needed in case you want to copy an image (use copyRangeContents_ ) or copy images, checkboxes, validations, formats, and formulas (copyRange_).

Jobs now also understands the new range type:

first free row

This will be handy if you need to paste values to the growing database. You do not know for sure about the last free row. Now it is not the case, just

Let Jobs do it for you =)