CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-macros
Hawsidog
Hawsidog

Reputation: 165

In Google Sheets how can I write a script to move a drawing to a specific location on the sheet?

I have a Google sheet with a drawing i.e. shape. What script would move the shape to a designated location on the sheet?

Here is a link to an example where I'd like to click on the blue rectangle and have the script move the green rectangle to cover cell A1.

https://drive.google.com/open?id=1eFCwDiY90ZM5SIMa6C0rSdhG_oC-SWgsk8vAvjIR34s

This is my first script in Google and I can't find a way to select the drawing.

Upvotes: 0

Views: 652

Answers (2)

emolano
emolano

Reputation: 73

This is how I did in my sheet and it worked; there may be a better way but at least this is easy to implement:

function placeDrawing(row, col, offsetY, offsetX){
  var ss = SpreadsheetApp.getActive().getSheetByName("sheetName");
  var drawings = ss.getDrawings(); //This returns an array of all drawings in the sheet 
  drawings[0].setPosition(row, col, offsetY,  offsetX);//for each drawing set the wanted position
}

Of course, you will need some testing to guess the exact index (mine were 0 and 1 of 8 drawings), or you can do a for to iterate them.

Upvotes: 0

Tanaike
Tanaike

Reputation: 201388

I believe your goal as follows.

  • You want to move the drawing, which is the green rectangle, to the cell "A1" when the blue rectangle is clicked.

For this, how about this answer?

Usage:

1. Set function name.

At first, please set the function name of myFunction to "the blue rectangle". By this, when the blue rectangle is clicked, myFunction is run.

2. Sample script.

function myFunction() {
  // 1. Retrieve sheet.
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");

  // 2. Retrieve "the green rectangle".
  const drawing = sheet.getDrawings().filter(e => e.getOnAction() != "myFunction")[0];

  // 3. Move "the green rectangle".
  drawing.setPosition(1, 1, 0, 0);
}
  • In this case, the blue rectangle has the function name of myFunction. So using this, the green rectangle is retrieved.
  • In order to run this script, please click the blue rectangle that myFunction was set. By this, the green rectangle is moved to the cell "A1".

Note:

  • This is a sample script. So please modify this for your actual situation.

Reference:

Upvotes: 1

Related Questions