How to Label A Drawing in Google Sheets to Reference with Google Script

Question

I'm trying to give the title/label "NewGrp" to a drawing I've created in my Google Sheets doc so that I can reference the drawing in Google Apps Script. In Excel, you simply go up to the named range box and name the drawing.

In Google Sheets, I can insert a drawing using Insert -> Drawing but it won't let me name the drawing.

I can also create a drawing in Google Drawings and name it "NewGrp" but then I can't insert this drawing into Google Sheets. (Google Sheets does not allow you to insert drawings from Google Drive it appears, unlike Google Docs).

How do I name a drawing in Google sheets so I can reference it in Google Apps Script?

Thanks Kim

J. G. · Answer 1 · 2021-08-23T18:53:46.780

I'm not sure if there is another way, but the way I have manipulated drawings in the past is by checking what function is tied to them.

If all you need is a label you should be able to pretend that the button is calling the "label" function without that function doing anything. This is a script for resetting the button positions.

An example:

function phaseTwoButtonPositions(){
  var drawings  = os.getDrawings();
  for (var i=0; i<drawings.length;i++)
    {
    var drawing = drawings[i];
    console.log("L208:%s",drawing.getOnAction(), drawing.getContainerInfo().getAnchorColumn());
    if (drawing.getOnAction() == "doThisThing") drawing.setPosition(8, 40,0,0);
    if (drawing.getOnAction() == "doThatThing") drawing.setPosition(8, 2,0,0);
    if (drawing.getOnAction() == "doAnotherThing") drawing.setPosition(11, 2,0,0);

    }
}

EDIT: This more specific for your situation. You do have to go through all of the drawings, check if their action matches, and set them accordingly. This moves the inactive group offscreen (40 columns over) depending on the state of the checkbox

function phaseTwoButtonPositions(){
  var drawings  = os.getDrawings();
  var checkbox = os.getRange("A1").getValue();//TRUE OR FALSE
  for (var i=0; i<drawings.length;i++)
    {
    var drawing = drawings[i];
    if (checkbox && drawing.getOnAction() == "group1") drawing.setPosition(8, 40,0,0);//move group one off screen
    if (checkbox && drawing.getOnAction() == "group2") drawing.setPosition(8, 2,0,0); //move group two on screen

    if (!checkbox && drawing.getOnAction() == "group2") drawing.setPosition(8, 40,0,0);//move group two off screen
    if (!checkbox && drawing.getOnAction() == "group1") drawing.setPosition(8, 2,0,0); //move group oneon screen

    }
}

Thank you. I have two drawings, call them Group 1 and Group 2. Both are groups of three buttons each. (I will program the individual buttons later). My goal is to have Group 1 buttons show if a specific field is checked on the sheet, and Group 2 buttons show if it is unchecked. So I don't think I can use a code that references all drawings on the page. I need a way to distinguish between the drawing "Group 1" and the drawing "Group 2". — Kim Hopkins, Aug 23 '21 at 18:32
So you can't show/hide buttons with code but you can do what I did.... move them 40 columns over. I'll update above with how I'd do it if you make a function called "groupOne" and assign it to groupOne — J. G., Aug 23 '21 at 18:50
How does your code know which drawing is Group 1 and which drawing is Group 2? Don't you need some way to label the drawings first? — Kim Hopkins, Aug 23 '21 at 20:23
to "label" the drawing, I right click the drawing, choose the three dots menu, "assign script" and then type in the label. You might have to create a dummy function in apps script with that function name (that doesn't do anything). Then the script here is checking what the "on action" script is and working based on that. — J. G., Aug 23 '21 at 20:27
Got it. Ok, I'm going to need to noodle on this with more coffee. And no way to hide and show? Here is the equivalent script in VBA for reference: — Kim Hopkins, Aug 23 '21 at 20:53

score 0 · Answer 2 · answered Aug 23 '21 at 21:00

0

Here is the code equivalent in VBA if helpful. Group 1 is one drawing and Group 2 is another, both on the same sheet.

.Shapes("Group1").Visible = msoCTrue ' Show Group 1 
.Shapes("Group2").Visible = msoFalse ' Hide Group 2

answered Aug 23 '21 at 21:00

Kim Hopkins

59
7

How to Label A Drawing in Google Sheets to Reference with Google Script

2 Answers2