Google Sheets Automation using Google Apps Script
Welcome. This is the second blog I am writing on Apps Script. In this blog post I am going to show you how to automatically change the background colour of a cell by specifying a value for it, via Apps Script.
The prerequisites are as follows:
- You should know how to use Google Forms.
- Basic working knowledge of Apps Script.
You can make a copy of the Google Form that I will be using in this article.
Before starting , if you need to refresh your knowledge on Google Forms then check out my earlier blog posts on it:
I have created my Google Form that takes feedback for the courses attended . In this form I am going to take their name, email address, rating for the course they attended and the name of the course that they attended .If their rating is below four then the colour of the cells will turn red. If the rating is above three then the colour of cells will turn green.
I am going to create the Google Sheet by clicking on the sheets icon that you see on the form, which contains the data.
Here you can see all the responses. Now we have to start writing code to change the colours . To open the Script Editor, you have to click on the Tools option, that shows the menu options as given below:
Click on the Script editor option, which will take you to the Apps Script editor, as shown below:
Now we have reached the Script Editor. You should first save the file. Name it as ChangeColor. Now we have everything ready, so let’s code.
Let me explain the different parts of the code now:
var sheet = SpreadsheetApp.getActiveSheet();var range = sheet.getRange(“D1:D10”);var rule = SpreadsheetApp.newConditionalFormatRule().whenNumberLessThan(4).setBackground(“red”).setRanges([range]).build();var rules = sheet.getConditionalFormatRules();rules.push(rule);sheet.setConditionalFormatRules(rules);
First up, I am going to get the active spreadsheet . Then I create a variable called range and then specify the range where my ratings are stored. After getting all the ratings we have to use newConditionalFormatRule . Here I have used the .whenNumberLessThan(4) , but there are various functions you can use. After this, I have written .setBackground(“red”) which changes the colour of the cell.You can put various colours inside the quotes.
After this we have to specify the range for which the colour has to be changed , by writing .setRanges([range]) and then put the range variable in the brackets, and in the end just put the build function.
After giving all the actions, now we have first get the actions and then set the actions to change the colours.To get the actions we have to assign a variable var rules = sheet.getConditionalFormatRules(); . Now we have to set the actions, rules.push(rule) by first writing push and then set it by writing sheet.setConditionalFormatRules(rules); .
Until now we have finished our first condition, that was to colour the cells in red if the number in them is less than or equal to 2. For the numbers greater than 3 we have to set the colour as green. The code is going to be exactly the same, other than the cell value function. Instead of using the .whenNumberLessThan(4) we are going to use the .whenNumberGreaterThan(3) function .
After this save , select the correct function and then run the code. After you run your code, go to your sheet, and you should see something like this.
Here the 1,2,3 ratings have been coloured red and the ratings 4,5 have been coloured in green.
I hope you have learnt how to colour cells in Google Sheets using Google Apps Script.
Do give me feedback. My email is aryanirani123@gmail.com.
