1

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 - ');
};
4
  • 2
    Welcome. Please edit your question to include your macro and describe your process and button details: what sheet, what cell, image or drawing, etc. Macros are simple/limited versions of a script but this question would require a full-blown Google Apps Scriptscript. @Tanaike has written a script on Disabling Buttons Put on Google Spreadsheet using Google Apps Script that might apply in your case. I'm not suggesting that you've got to solve this alone, but you do need to provide more information so that we can help.
    – Tedinoz
    Oct 27, 2020 at 1:29
  • I've added the very short macro code as requested. There is only one sheet in this workbook. I simply want the copy to not have the button on it. If you're able to help me delete the button via the macro that would be great. Thanks.
    – Rob
    Oct 28, 2020 at 8:02
  • Is the button a “drawing“ or an “image“? Are there any other drawings or Images in the spreadsheet?
    – Tedinoz
    Oct 28, 2020 at 8:53
  • Yes, the button is a drawing object and there are no other objects on the sheet. I just want to find a way to select and delete the button with the macro, but haven't been able to find how to select the object as part of a macro.
    – Rob
    Oct 28, 2020 at 16:52

1 Answer 1

1

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()

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.