how to prevent users from moving the shapes on google sheet or change the assigned macro for the

Question

i have Google sheet for adding, updating , and deleting some information for patients , i want to prevent the users from changing the assigned script of these shapes and prevent them from changing the position for them

I want to prevent the users from changing the assigned script of these shapes and prevent them from changing the position for them

Answer 1

I am unsure if it is possible to restrict editing of inserted drawings/shapes on a sheet however having a similar issue of scripts getting unassigned accidently I created a function that can assign scripts, this is then on a trigger as often as needed, for me each day.

The current assumption for the function is that there is only 1 drawing/shape per sheet and this is selected by the var num = 0 selecting the first drawing. This would have to be adjusted if you had multiple per sheet.

function setDrawings() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var num = 0;

  function updateSheetActions(sheetName, scriptName) {
    var sheet = ss.getSheetByName(sheetName);
    var drawings = sheet.getDrawings();
    var beforeAction = drawings[num].getOnAction();

    if (beforeAction !== scriptName) {
      drawings[num].setOnAction(scriptName);
    }
  }

  var sheetsAndScripts = [
    { sheetName: "Sheet1", scriptName: "script1" },
    { sheetName: "Sheet2", scriptName: "script2" }
  ];

  sheetsAndScripts.forEach(function (item) {
    updateSheetActions(item.sheetName, item.scriptName);
  });
}

You'd need to adjust the Sheet1 / Script1 etc. with your relevant sheets and scripts.

Ref:

Drawing Class

getDrawings

setOnAction