Copying cell formatting inconsistently between functions in Google Sheets

Other Questions Google Sheets
1

I have two Google Apps Script functions that should work the same way, but one copies formatting properly while the other doesn’t. Both functions grab calendar data from monthly sheets and put it on a display sheet called “ViewCalendar”. The moveNext function works fine and copies both values and formatting. But the movePrevious function only copies the values and ignores the formatting completely. I can’t figure out why they behave differently when the code looks almost identical. Has anyone run into this before?

function moveNext() {
    var workbook = SpreadsheetApp.getActive();
    var currentMonth = workbook.getRange('D20').getValue() + 1;
    var nextMonth = currentMonth + 1;

    workbook.getSheetByName(currentMonth).getRange('A3:G16').copyTo(workbook.getSheetByName('ViewCalendar').getRange('A3:G16'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    workbook.getSheetByName(currentMonth).getRange('A3:G16').copyTo(workbook.getSheetByName('ViewCalendar').getRange('A3:G16'), SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
    workbook.getSheetByName(nextMonth).getRange('A3:G16').copyTo(workbook.getSheetByName('ViewCalendar').getRange('I3:O16'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    workbook.getSheetByName(nextMonth).getRange('A3:G16').copyTo(workbook.getSheetByName('ViewCalendar').getRange('I3:O16'), SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);

    workbook.getActiveSheet().getRange('D20').setValue(currentMonth);
}

function movePrevious() {
    var workbook = SpreadsheetApp.getActive();
    var currentMonth = workbook.getRange('D20').getValue() - 1;
    var nextMonth = currentMonth + 1;
    
    workbook.getSheetByName(currentMonth).getRange('A3:G16').copyTo(workbook.getSheetByName('ViewCalendar').getRange('A3:G16'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    workbook.getSheetByName(currentMonth).getRange('A3:G16').copyTo(workbook.getSheetByName('ViewCalendar').getRange('A3:G16'), SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
    workbook.getSheetByName(nextMonth).getRange('A3:G16').copyTo(workbook.getSheetByName('ViewCalendar').getRange('I3:O16'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    workbook.getSheetByName(nextMonth).getRange('A3:G16').copyTo(workbook.getSheetByName('ViewCalendar').getRange('I3:O16'), SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);

    workbook.getActiveSheet().getRange('D20').setValue(currentMonth);
}
2

the issue’s probly sheet names getting converted to numbers. when currentMonth hits 0 or goes negative, you won’t have a sheet called “0”. add some debug logging to check what sheet names ur actually trying to hit. double-check that ur sheet names match exactly what ur variables are spitting out.

3

Had this exact problem last year. The formatting breaks in movePrevious because Google Sheets gets weird with sheet references when they’re calculated at different times. Your code might look identical, but Google processes the range references at different moments, so the formatting commands hit stale or invalid sheet objects. Store your sheet references in variables before copying anything. Like var sourceSheet = workbook.getSheetByName(currentMonth); then use sourceSheet.getRange() instead of chaining everything together. Forces Google to grab the sheet reference once and reuse it - should fix the formatting mess. If that doesn’t completely work, throw in Utilities.sleep(100) between the value and format paste operations.

4

I’ve encountered a similar issue with Apps Script. The root cause often lies in referencing sheets that may not exist. In your movePrevious function, if currentMonth becomes 0 or negative, getSheetByName() will fail silently or return null, which leads to it skipping the formatting while still processing the values from the cache. It’s crucial to add some validation first; ensure that the sheet names exist by checking if workbook.getSheetByName(currentMonth) !== null. Additionally, consider incorporating console.log() statements to monitor the values of currentMonth and nextMonth during execution. This approach will help you pinpoint exactly when the code attempts to access non-existent sheets.