CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-api
DBI
DBI

Reputation: 23

Move a drawing to a specific location

I am trying to write a script to move a drawing (over cells) to a specific location. I tried this method on my google sheet and it didn't work: In Google Sheets how can I write a script to move each drawing to a specific location on the sheet?

Do you have any idea why?

enter image description here

The code I've tried:

const obj = {
    "10": {name: " Drawing1 ", moveTo: [1,1]}, 
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet1");
sheet.getDrawings().forEach(d => {
 const arow = d.getContainerInfo().getAnchorRow();
 if (arow in obj) {
      d.setPosition(...obj[arow].moveTo, 0, 0);
 }
  })

Upvotes: 2

Views: 1054

Answers (1)

Kris
Kris

Reputation: 567

I tested this and it works on my end with a couple of edits.

How it works The code references the object at the top. It loops through each drawing, and finds the row that contains the top of the drawing. It then looks up that row in the object, if it finds the row in the object, it moves the drawing to the coordinates specified in the 'moveTo' key.

If the drawing don't have a row listed in the object, it will skip over them.

There was a mistake in the code.

const obj = {
    "10": {name: " Drawing1 ", moveTo: [1,1]},

Should be this:

  const obj = {
                "10": {name: " Drawing1 ", moveTo: [1,1]}
              };

The second ending curly brace was omitted. That may have been from editing an object that had more elements in it, so may not be the issue.

Lastly, I added a statement to log the variable 'arow' for each drawing, this way you can check if the row is in your object at the top.

function moveDrawing(){
  const obj = {
                "10": {name: " Drawing1 ", moveTo: [1,1]}
              };
  
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet1");

  sheet.getDrawings()
    .forEach(d => {
      const arow = d.getContainerInfo().getAnchorRow();
      if (arow in obj) {
        d.setPosition(...obj[arow].moveTo, 0, 0);
      }
    Logger.log(arow);
    }  
  )
}

I tested it with two drawings, and it works if they are in the rows specified in the object.

Before:

Image before running code

After: Image after running code

This is the code I used in the above example:

function moveDrawing(){
  const obj = {
                "3": {name: " Drawing1 ", moveTo: [1,1]},
                "4": {name: " Drawing2 ", moveTo: [1,5]}
              };
  
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet1");

  sheet.getDrawings()
    .forEach(d => {
      const arow = d.getContainerInfo().getAnchorRow();
      if (arow in obj) {
        d.setPosition(...obj[arow].moveTo, 0, 0);
      }
    Logger.log(arow);
    }  
  )
}

Upvotes: 3

Related Questions