Google-sheets – How to select and remove drawing object in macro

google sheetsgoogle-sheets-macro

I have a Google Sheet template with a button that triggers a macro to make a new copy of itself that can than be edited (code below). I'd like the macro to remove the button on its copy, as there's no need to make a copy of the copy. However, when I tried to record the steps to delete the button, the code doesn't appear in the resulting macro.

Is there code that can be added to my CopySheet macro that remove the button object?

Any help would be appreciated.

function CopyDA() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A1').activate();
  spreadsheet.copy('Deal Analyser - ');
};

Best Answer

You want to copy a spreadsheet template by clicking a button, and then delete the button in the newly copied spreadsheet.

You are using a macro to copy the spreadsheet. A macro is a Google Apps Script that is automatically created by user UI interactions. Macros have their strengths and weaknesses but in this case, a macro can't manage the steps required to delete the button on the new spreadsheet. You will need to substitute the macro for a script like the one below.

  • Copy the script to the Script Editor (Tools > Script Editor).
    • There should be two tabs: "Code" and "macros". Copy the script below, and paste it into "Code" tab (you can delete the "myFunction()" function).
    • You can rename the function is you wish.
  • Save the editor contents (File > Save)
  • Close the editor using the Browser "close" button
  • Right click the button, click the edit icon and enter the name of the new function under the "Assign Script option.
function wa14811801() {

  // define this spreadsheet and sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  sheet.getRange('A1').activate();
  
  // copy the spreadsheet and return the ID of the new spreadsheet
  var newsheet = ss.copy('Deal Analyser - ').getId(); 
  Logger.log("DEBUG: the ID of the new spreadsheet = "+newsheet)
  
  // open the new spreadsheet for editing
  var newtemplate = SpreadsheetApp.openById(newsheet);

  // get the first sheet in the copy of the template
  var newtemplatesheet = newtemplate.getSheets()[0];
  
  // get the drawings
  var drawing = newtemplatesheet.getDrawings();
  Logger.log("DEBUG: the number of drawings is "+drawing.length)
   
  // remove the first drawing
  drawing[0].remove();
   
  // end
  return;
  
}

Logic

  • var newsheet = ss.copy('Deal Analyser - ').getId(); - a variable is assigned to the result and getid() gets the unique identifier for the newly creatred spreadsheet
  • var newtemplate = SpreadsheetApp.openById(newsheet); - opens the newly created spreadsheet for editing by using openById(id)
  • var newtemplatesheet = newtemplate.getSheets()[0]; - gets the first (and only) sheet from the newly created spreadsheet.
  • var drawing = newtemplatesheet.getDrawings(); - gets all the drawings on the first sheet.
  • drawing[0].remove(); - deletes the first drawing from the spreadsheet.

References

getId()

openById().

getSheets()

getDrawings()

remove()

Related Topic