To apply formatting to cells in a Google Sheet using Apps Script, you can use the setNumberFormat() and setBackgroundColor() methods of the Range class. Here’s an example code snippet that demonstrates how to apply formatting to cells:
function applyCellFormatting() { var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var sheet = spreadsheet.getActiveSheet(); var range = sheet.getRange('A1:B5'); // Replace with the desired range // Apply number format range.setNumberFormat('0.00'); // Replace with the desired number format // Apply background color var backgroundColor = '#FFFF00'; // Replace with the desired background color range.setBackgroundColor(backgroundColor); }
In this code, the applyCellFormatting function first retrieves the active spreadsheet using SpreadsheetApp.getActiveSpreadsheet(). You can modify this line to access a specific spreadsheet if needed.
Next, the active sheet is obtained using getActiveSheet().
You can then define the range of cells you want to apply formatting to using the getRange() method. In this example, the range is set to 'A1:B5', but you should change it to your desired range.
To apply number formatting, use the setNumberFormat() method on the range object. Replace '0.00' with the desired number format, such as '0%' for percentage formatting or 'yyyy-mm-dd' for date formatting.
To apply background color, set the backgroundColor variable to the desired color code. In this example, it’s set to '#FFFF00', which represents yellow. You can change it to any valid color code.
Finally, call the setBackgroundColor() method on the range object, passing the backgroundColor variable as an argument, to apply the background color to the cells.
You can call the applyCellFormatting function from another function or set up a trigger to execute it based on your needs. When executed, it will apply the specified formatting to the cells in the range.
Feel free to modify the code to suit your specific formatting requirements, such as applying font styles or borders, and work with different ranges and formatting options.