0

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?

  • We could 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. – Rubén Dec 29 '19 at 0:56
0

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.

  • Are you telling me, or are you telling the folks at Google who wrote the Macro Recorder that Sheet10!: isn't a valid range? i.e. in your opinion, is this a bug for the Google folks? – zipzit Dec 29 '19 at 0:31
  • I'm telling this to the people who read this Q/A. – Rubén Dec 29 '19 at 0:32
0

Many thanks to Rubén for recognizing that

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

But I'm really looking for a solution to the dilemma of WHY the "Record Macro, Play Macro" Process fails, and ensuring others don't get stuck with the same issue. Clearly Rubén has captured the symptom of the issue. And I am grateful for his reply. Were it not for him, I would not have figured out what is causing the failure; but his answer does not address the root cause of the issue. As a Google Sheets user, my expectation is that a recorded macro exactly duplicates the actions recorded. And if not, why not?

The root cause of the issue is that the Google sheet Macro Recorder does not recognize the technique of "Select All" by clicking in the upper left hand corner (Above the Row #'s and Left of Column Headers ABC's). I believe this is a bug / fail on the part of the folks who wrote the Google Sheet macro recorder. And the "Range Not Found" error message isn't nearly as useful as an "Macro Unable to Record, Selection Process Used is Not Supported" error message.

Note: This same test performed on an Microsoft Excel spreadsheet (Developer --> Record Macro, copy, paste, end record) works great.

Here's a possible "work around" for the "Select All" issue. One can group select all of the columns that contain data. In my case, this generates

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

This works fine for my use case, as I have a static number of columns to copy (even though each time I run the macro, there are a different number of rows involved). The select all columns trick works fine.

Problem has now been reported to Google including link to this question. Unfortunately there is no bug tracking info that I can see anywhere.

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.