Macro stopped functioning after months of use

[Timothy Hartle]

This simple Macro script ran fine for months. On Monday and Tuesday, it was generating an error:

function AddToSummary() {

  var spreadsheet = SpreadsheetApp.getActive();

  spreadsheet.getRange('C4').activate();

  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Summary'), true);

  spreadsheet.getRange('A1').activate();

  spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();

  spreadsheet.getCurrentCell().offset(1, 0).activate();

  spreadsheet.getRange('\'Deposit Recap\'!C4').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  spreadsheet.getCurrentCell().offset(0, 1).activate();

  spreadsheet.getRange('\'Deposit Recap\'!B5').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  spreadsheet.getCurrentCell().offset(0, 1).activate();

  spreadsheet.getRange('\'Deposit Recap\'!B4').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

   spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Deposit Recap'), true);

  spreadsheet.getRange('B6').activate();

};

 

The error generated is:

Error: The parameters (RangeApiAdapter,nuII, Boolean) don't match the method

signature for SpreadsheetApp.Range.copyTo.

 I have no Boolean operators in the function. When I edit the script, highlight the function and run the debugger, I get this:

Execution log

4:08 PM               Notice   Execution started

4:08 :36 PM        Error      TypeError : started a.keys(... ) . va is not a function

ClearAll @ macros. Gs:9

 

When I click on the macro.gs: link it takes me to a different function in the script. And, now when I run that macro in the script, I get this error (same as the debugger):

 TypeError: a.keys(O.va is not a function

I reverted the sheet back three weeks and still get the same errors.

Does anyone have any idea why this has started happening? I read in one of the threads that:

Custom functions use Apps Script, and there are daily quotas and limitations. If you exceed a quota or limitation, the function errors out.

Could this be the problem? The function is only used 8 or 9 times a day at most.

Last evening I was getting the errors. This morning when I ran the macro, it finished without errors.

Thanks for your help.

[AliceKeeler Keeler]

Try updating your copyTo

spreadsheet.getRange('\'Deposit Recap\'!C4').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES);

I tested it with some sample data, worked for me. Here is the full script. 

function AddToSummary() {
  const spreadsheet = SpreadsheetApp.getActive();
  const summarySheet = spreadsheet.getSheetByName('Summary');
  if (!summarySheet) return;

  spreadsheet.setActiveSheet(summarySheet, true);

  const lastCell = summarySheet.getRange('A1').getNextDataCell(SpreadsheetApp.Direction.DOWN);
  const nextRow = lastCell ? lastCell.getRow() + 1 : 2;
  let targetRange = summarySheet.getRange(nextRow, 1);
 
  const depositRecap = spreadsheet.getSheetByName('Deposit Recap');
  if (!depositRecap) return;

  depositRecap.getRange('C4').copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_VALUES);
  targetRange = targetRange.offset(0, 1);
  depositRecap.getRange('B5').copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_VALUES);
  targetRange = targetRange.offset(0, 1);
  depositRecap.getRange('B4').copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_VALUES);

  spreadsheet.setActiveSheet(depositRecap, true);
  depositRecap.getRange('B6').activate();
}

[Timothy Hartle]

Alice:

Thanks for taking the time to look at this for me. I appreciate it.

I looked at the change you suggested to see that your only difference was removing ,false at the end of the CopyPasteType.PASTE_VALUES function. I gave it a try and script  ran OK; but, oddly, the border formatting of the destination cells changed. When I added the ,false back in, the borders were unchanged (as desired).

So, I guess my question remains. Why does the script function properly most of the time and, occasionally, throw errors? As of this moment, the original script is running perfectly. It's as though something with Google sheets temporarily changed.

Before posting, I tried the script on Macs and PCs. On one of the PCs I tried different browsers (Edge & Chrome) and cleared the browser cache. I got the same results. On Tuesday, the script worked fine on two of the Macs at first. Later in the day I got the error. I'm perplexed.

Again, thanks for your help.

Tim

--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/cPe2w8j6r4c/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/d825b621-7db8-4933-87c2-714e99ab5ff6n%40googlegroups.com.