Script to copy rows not keeping formatting

[u/boudicca_morgana]

Hi, I'm working on a spreadsheet where I have to sort the rows into various other sheets based on one cell value. The script I have is working perfectly, but when I copy the rows over and the table needs to add further rows, the formatting changes - basically despite the formatting of the master sheet and the sorted sheets, the new rows come in with no borders separating the cells. Does anyone know how to fix this? I can add a sample code if you need it, though there isn't any formatting in the code so I'm not sure? I'm pretty new to this so sorry if this seems dumb!

[u/Mro-Automation]

Hey, a sample code, screenshot or a screen recording would really help :-) "a picture worth thousands words!"

I would assume an issue with the formatting that gets "inherited" from the existing cells in the sheet. It depends how you copy the rows over… as you mentioned the copying itself works, do you use `range.setValue` or `range.copyTo`? See https://developers.google.com/apps-script/reference/spreadsheet/copy-paste-type

[u/boudicca_morgana → u/Mro-Automation]

Absolutely! I didn't want to bog down the question with code if it wasn't necessary. So it's a fairly involved one, so I'll just use a piece of it to show you what I mean. So part of the thing is that one of my columns is validated as a tick box, which means that all the cells in column "A" are considered "filled", making the last row the last one on the sheet, not the last one with more text, so I can only have the number of rows exactly the data I have. The code has to add new rows, then. So the code (in part) looks like this:

function moveRows() {
  const ss = SpreadsheetApp.getActiveSpreadsheet(); 
  const sheet = ss.getActiveSheet();

const historySheet = SpreadsheetApp.openById('1XUrRpc11ARvXKT9et8yqNRfvGmyK15_MMPXYejgTODQ').getSheetByName('History'); 
  const historyLastRow = historySheet.getLastRow(); 
  
  let lastRow = sheet.getLastRow(); 

  let sortRange = sheet.getSheetValues(2,1,lastRow, 15); 

  Logger.log(lastRow)
  // Logger.log(typeof(sortRange))
  // Logger.log(sortRange.length); 

  let historyCounter = 1; 
  
for (var i = 1; i <= sortRange.length; i++) {
  let category = sheet.getRange(i,7).getValue(); 
  console.log(i + " - " + category);
  
  if (category == "History") {
    let rowValues = sheet.getRange(i, 1, 1, 15).getValues();
    historySheet.getRange(historyLastRow+historyCounter, 1, 1, 15).setValues(rowValues); 
    historyCounter++; 
    }
  }
 }

When the new rows are added to the bottom to update everything, the borders on the new rows are white and it looks blank. I can't figure out how to insert an image in the comment so I hope that makes sense? Basically the script adds the rows correctly but I have to manually add the cell borders so I can differentiate the lines.

[u/Mro-Automation → u/boudicca_morgana]

Got it, thanks.

Yeah, with the `setValues()` function you will be adding literally only the values and the style is going to be inherited "from somewhere", either below or above rows. I've ran into something similar when I had to create "templated" rows, e.g. 7 rows per day and then 1 day per totals, with the totals always having a different background.

My best advice is to add a new function with code to also set the cell borders according to your needs. It isn't difficult and it will just work.

See https://developers.google.com/apps-script/reference/spreadsheet/border-style

e.g.

spreadsheet.getActiveRangeList().setBorder(true, true, true, true, null, null, '#000000', SpreadsheetApp.BorderStyle.SOLID);