Visibility of drawing based on function

user_13612504 0
Viewed 94

I want to create a chart / flow diagram in Google Sheets. Now I was wondering if I could make it a bit more dynamic based on certain filters.

So my goal is to show certain drawings / arrows based upon these filters. Anyone an idea if that is possible and if yes, how this can be done?

Filter off

Filter on

google-sheets
1 Answers

You can do it with a custom script which changes the z-index values of each drawing element.

First, insert the following drawings by hand:

  1. 1st drawing element should be a background which covers the entire area of the diagram
  2. 2nd drawing element should be the static drawing (the base of the drawing that doesn't change)
  3. 3rd drawing element should be the dynamic part that shows or hides when a checkbox is ticked/unticked

Then, using Goggle scripts, write a function that does the following:

  1. Use getDrawings() method to get an array of all the drawings, for example:
    var drawings = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getDrawings();
  2. Use setZIndex() method to assign z-index values to the 1st (background) and the 2nd (static) layers, for example:
    - drawings[0].setZIndex(100);
    - drawings[1].setZIndex(200);

Then, all you have to do is play with the z-index value of drawings[2] which is your 3ed (dynamic) drawing element and change it between 0 and 300 based on the value of the cell containing the checkbox (TRUE or FALSE).

When the z-index of the 3ed (dynamic) drawing layer is set to 0, it will move behind the background, rendering it invisible. When set to 300 it will move to the front.

user_26973590
Related