CODEWITHSUNDEEP
google-apps-script
Freddy Roller
Freddy Roller

Reputation: 127

Google App Script Change Background Color using Color Picker from menu item selection

I want to implement a menu item that when selected will pop up the color picker and then change the background color of certain cells on the sheet to the color that was selected.

I have successfully been able to accomplish all of these things in my script but I do not have a good way for the user to easily pass a color that I can use to change the background. (I want to avoid having them required to change a certain cell to the color they want and then click the menu item to change the rest of the sheet)

Here is my current code:

//Function to create menu item
function onOpen(){
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Sheet Options')
      .addItem('Change Background Color', 'colorMe')
      .addToUi();
}  

function colorMe(){
   var spreadsheet = SpreadsheetApp.getActive();

   // Code to get color value selected by user from ui color picker 
   // Replace #000000 with that value

   spreadsheet.getRangeList(['A:A', '1:1', 'H:H', 'I:I', 'N:U', 'B8:G9', 'E10:E31', 'F14:G31', 'B32:G100', '101:142']).activate()
   .setBackground('#000000');  

}

Upvotes: 0

Views: 2935

Answers (2)

Tanaike
Tanaike

Reputation: 201338

  • You want to change the background color of cells on Spreadsheet.
    • The ranges are 'A:A', '1:1', 'H:H', 'I:I', 'N:U', 'B8:G9', 'E10:E31', 'F14:G31', 'B32:G100', '101:142'.
  • You want to select the color by a color picker.
  • You can use a dialog and sidebar.

If my understanding is correct, how about this sample script? In this answer, <input> elements with type color is used. Please think of this as just one of several answers.

Sample script:

HTML: index.html

Please copy and paste the following script to the HTML of the script editor.

<input type="color" id="color" value="#ffffff">
<script>
document.getElementById("color").addEventListener("change", function(e) {
  google.script.run.colorMe(this.value);
});
</script>

Google Apps Script: code.gs

Please copy and paste the following script to the script of the script editor.

function openDialog() {
  var html = HtmlService.createHtmlOutputFromFile("index");
  SpreadsheetApp.getUi().showModalDialog(html, "sample");
}

function colorMe(color) {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet
  .getRangeList(['A:A', '1:1', 'H:H', 'I:I', 'N:U', 'B8:G9', 'E10:E31', 'F14:G31', 'B32:G100', '101:142'])
  .activate()
  .setBackground(color);
}
  • When you run the function of openDialog(), a dialog is opened to the active Spreadsheet. And, when you click the color picker and select the color and click "OK", the color code is retrieved and colorMe is run. Then, the background color of cells are changed to the selected color.

Note:

  • This is a simple sample script. So please modify this for your situation.

References:

Upvotes: 5

Freddy Roller
Freddy Roller

Reputation: 127

I was able to find the following article which helped me to implement a solution https://yagisanatode.com/2019/08/07/google-apps-script-google-standard-color-palette-picker-for-sidebar-and-dialog-boxes-in-google-sheets-google-docs-and-google-slides/

Upvotes: 0

Related Questions