Format issue when copying data in Sheets script

Other Questions Google Sheets
1

Hey guys, I’m having trouble with a Google Sheets script. I’m trying to copy calendar data from two monthly sheets to a ‘TwoMonth’ sheet. I’ve got two functions: one moves forward a month, the other goes back.

The weird thing is, the forward function works fine, but the backward one only copies the values, not the formatting. I can’t figure out why. Here’s a simplified version of what I’m doing:

function moveMonth(direction) {
  var ss = SpreadsheetApp.getActive();
  var currentMonth = ss.getRange('A1').getValue();
  var newMonth = direction === 'forward' ? currentMonth + 1 : currentMonth - 1;
  
  var sourceRange = ss.getSheetByName(newMonth).getRange('B2:H15');
  var targetRange = ss.getSheetByName('TwoMonth').getRange('B2:H15');
  
  sourceRange.copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_VALUES);
  sourceRange.copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_FORMAT);
  
  ss.getRange('A1').setValue(newMonth);
}

Any ideas why the backward function isn’t copying the format? Thanks!

2

I’ve run into this exact problem before, and it drove me nuts for days. What finally worked for me was switching to the copyTo method with the SpreadsheetApp.CopyPasteType.PASTE_NORMAL option. This copies both values and formatting in one go. Here’s how I tweaked the code:

sourceRange.copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_NORMAL);

Also, double-check that your source and target ranges are exactly the same size. Even a slight mismatch can cause weird formatting issues. If all else fails, try clearing the formatting on the target range before copying:

targetRange.clear({formatOnly: true});

Hope this helps! Let us know if it solves your problem.

3

hey creativepainter33, seems like your backward sheet dont have active formatting. try manully applying a format in that sheet and rerun. if it still fails, maybe merge copy functions or adjust your copyTo options.

4

I’ve faced similar issues before. In my experience, the script might fail to apply formats due to sheet protection settings or the presence of conditional formatting rules that aren’t handled by the copyTo method. I recommend checking if the backward month sheet is protected or if there are conditional formatting rules that conflict with your script. Temporarily removing these protections or rules might help. Also, verifying that the source and target ranges match exactly could resolve the discrepancy between copying values and formats.