google-sheets google-apps-script google-workspace

ReferenceError: C is not defined

I built this spreadsheet to manage all my familly's expenses: https://docs.google.com/spreadsheets/d/1UBGZj1zuZSUzYWH2vpitfLLud32e-efKKVuKQ2UWPrA/edit?usp=sharing

Until a month a go it worked like charm. But since then, out of nowhere all my macros started showing: "ReferenceError: C is not defined" and stopped working. Nothing has changed and i made it work for months before is stopped for no reason. I run several scripts on this sheet and they all stopped working showin the same error.

Can someone pleas help me figure this out? An explain to me why it suddently stopped working?

I have no coding experience and generated these scripts mostly out of Chat GPT and macro recording on Gsheet, therefor don't hesitate to adress me as a muggle.

Thanks in advance!

Here are a few examples of scripts (don't hesitate to go on the appscript page from the sheet which is open for further details):

function InsertCode() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('8:8').activate();
  spreadsheet.getActiveSheet().insertRowsBefore(spreadsheet.getActiveRange().getRow(), 1);
  spreadsheet.getActiveRange().offset(0, 0, 1, spreadsheet.getActiveRange().getNumColumns()).activate();
  spreadsheet.getRange('B8').activate();
  spreadsheet.getRange('C2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  spreadsheet.getRange('C8').activate();
  spreadsheet.getRange('C4').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  spreadsheet.getRange('8:8').activate();
  spreadsheet.getRange('9:9').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
  spreadsheet.getRange('C2:C4').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  spreadsheet.getRange('E8:F8').activate();
  spreadsheet.getRange('E8:F8').deleteCells(SpreadsheetApp.Dimension.ROWS);
};

function HIDE() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('G6').activate();
  spreadsheet.getActiveSheet().hideSheet();
};

function Format_Import_UBS_Account() {
  var spreadsheet = SpreadsheetApp.getActive();
  var originalSheet = spreadsheet.getActiveSheet(); // Save reference to the original active sheet

  // Step 0: Ensure the current sheet has enough rows and columns
  originalSheet.insertRowsAfter(originalSheet.getMaxRows(), 1000); // Add 1000 extra rows if needed
  originalSheet.insertColumnsAfter(originalSheet.getMaxColumns(), 20); // Add 20 extra columns if needed

  // Step 1: Activate cell O10 in the original sheet
  spreadsheet.getRange('O10').activate();

  // Step 2: Switch to the IC_Triage sheet
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('IC_Triage'), true);
  spreadsheet.getRange('O10').activate();

  // Step 3: Select the data range from O10 to the last column with data
  var currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.NEXT).activate();
  currentCell.activateAsCurrentCell();

  // Step 4: Select the entire data range from O10 down to the last row with data
  currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();

  // Step 5: Switch back to the original sheet and paste formulas and formatting from IC_Triage
  spreadsheet.setActiveSheet(originalSheet, true);
  spreadsheet.getRange('IC_Triage!O10:Z2314').copyTo(originalSheet.getRange('O10'), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

  // Step 6: Select the range again and paste values in the original sheet
  spreadsheet.getRange('O10').activate();
  currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.NEXT).activate();
  currentCell.activateAsCurrentCell();
  currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  spreadsheet.getRange('O10:Z2314').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

  // Step 6.1: Copy and paste range P10:Q2314 from IC_Triage to the same range in the original sheet
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('IC_Triage'), true); // Go to the IC_Triage sheet
  var rangeToCopy = spreadsheet.getRange('P10:Q2314'); // Select range P10:Q2314
  rangeToCopy.copyTo(originalSheet.getRange('P10:Q2314'), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false); // Paste into the same range in the original sheet

  // Step 7: Delete the first 9 rows in the active sheet
  originalSheet.deleteRows(1, 9);

  // Step 8: Delete specified columns in the active sheet
  originalSheet.deleteColumns(1, 4);  // Delete columns A to D
  originalSheet.deleteColumns(2, 4);  // Delete columns B to E
  originalSheet.deleteColumns(5, 2);  // Delete columns D to E
  
  // Step 9: Move columns to new positions
  originalSheet.getRange('K:N').activate(); // Activate columns K to N
  originalSheet.moveColumns(originalSheet.getRange('K:N'), 1); // Move columns K:N to the start (position 1)
  originalSheet.getRange('I:I').activate(); // Activate column I
  originalSheet.moveColumns(originalSheet.getRange('I:I'), 5); // Move column I to position 5
  originalSheet.getRange('K:K').activate(); // Activate column K
  originalSheet.moveColumns(originalSheet.getRange('K:K'), 6); // Move column K to position 6
  originalSheet.getRange('K:L').activate(); // Activate columns K to L
  originalSheet.moveColumns(originalSheet.getRange('K:L'), 7); // Move columns K:L to position 7
  originalSheet.getRange('N:N').activate(); // Activate column N
  originalSheet.moveColumns(originalSheet.getRange('N:N'), 9); // Move column N to position 9
  originalSheet.getRange('J:K').activate(); // Activate columns J to K
  originalSheet.moveColumns(originalSheet.getRange('J:K'), 17); // Move columns J:K to position 17
  originalSheet.getRange('P:P').activate(); // Activate column P
  originalSheet.moveColumns(originalSheet.getRange('P:P'), 18); // Move column P to position 18

  // Step 10: Adjust formatting and column widths
 originalSheet.setFrozenRows(0); // Unfreeze any frozen rows
 originalSheet.autoResizeColumns(1, 30); // Auto resize the first 30 columns
 originalSheet.setColumnWidths(10, 2, 286); // Set specific column widths for J and K
}

I tried changing the script but chat GPT was not very helpfull and i don't know how to define C.

Solution

I have noticed in your current code that there is a letter C above the function InsertCode()

/** @OnlyCurrentDoc */
C
function InsertCode() {

which causes the error:

ReferenceError: C is not defined

I have tried to delete it and run the code, and it works as expected. I have a hunch that you just placed it accidentally. Kindly try to run the code without it and see if it is indeed the issue you were having.

In such cases that you encounter an error, please try to check the @ macros.gs:[line number] below the error as it will dictate the lines where it does have an issue.