Google-sheets – Simple Google Sheet Macro. Save it, Run it, Fail it… Range Not Found

google sheetsgoogle-sheets-macro

Not sure what's going on here. I want to copy the data from one tabbed "sheet" within my Google Sheet to another.

I set up Record macro. Navigate to data sheet tab. Copy all (via click in top left corner, above row numbers, left of column letters, right click, copy). Go to different named blank tab within the same google sheet document. Select cell A1, Right Click Paste. End record macro.

Macro reads: 

function Macrotest() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A1').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('DataTab'), true);
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('OutputTab'), true);
  spreadsheet.getRange('Sheet10!:').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
};

So far, so good. Clear out 'OutputTab' content. Run macro 'MacroTest()' directly from Tools Menu. Running Script shows in green alert box for 0.5 seconds, followed by red alert box, Range Not Found.

Huh? How is it possible that a recording of four of five mouse clicks can't repeat all within the same Google Sheet? What's going on here? Note: I don't want to duplicate() the tab, as the OutputTab has significant print formatting setups.

Is this a bug in the Google Sheets Recorder? Why would a simple record macro not function when asked to run?

Best Answer

There is an error on the following code line:

spreadsheet.getRange('Sheet10!:').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

'Sheet10!:' isn't a valid reference for a range.

I was able to reproduce this.

Please use Google Feedback (Open a spreadsheet, click the Help menu then click on Report a problem) to send to Google your feedback / bugs reports / feature requests.

Related Topic