I'm trying to take all of the contents in a google sheet from columns G:M and send it as values while keeping the conditional formatting, colors and number formatting correct to an email once a week. I've tried a few variations and keep getting weird results. Does anyone have any recommendations?
Thank you.
You can create a function to copy the format to the cells
function copyCellsFormat(source, destination) {
var ss = SpreadsheetApp.getActive();
// Get the cells
var sourceSheet = ss.getSheetByName(source);
var range = sourceSheet.getRange(1, 1, sourceSheet.getMaxRows(), sourceSheet.getMaxColumns());
// The target of data formatting rules
var destinationSheet = ss.getSheetByName(destination);
range.copyFormatToRange(destinationSheet, 1, sourceSheet.getMaxColumns(), 1, sourceSheet.getMaxRows());
}
Feel free to check the documentation on https://developers.google.com/apps-script/reference/spreadsheet/range#copyFormatToRange(Sheet,Integer,Integer,Integer,Integer)
Or if you want to build the conditional formatting rules programmatically, you might want to check on https://developers.google.com/apps-script/reference/spreadsheet/conditional-format-rule-builder
// Adds a conditional format rule to a sheet that causes cells in range A1:B3 to turn red if
// they contain a number between 1 and 10.
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1:B3");
var rule = SpreadsheetApp.newConditionalFormatRule()
.whenNumberBetween(1, 10)
.setBackground("#FF0000")
.setRanges([range])
.build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);