Google Sheets - Add to Certain Value Based on Background Color

0
votes

I've tried making a Google Apps Script, but I was having trouble trying to understand how to set it up. From this it seems like I can create a function that I can call inside the spreadsheet itself like the SUM function provided by Google Sheets. I've taken a look at the getBackground() function, but it seems like it needs some global variables included instead of just functions.

Here's my current spreadsheet: spreadsheet

I want to input a function where it takes in the ranges A2:A1000 and based on the background color of the cell, determine whether it goes into "Work" or "Life" and then adds it onto the cells E4 (Total Work) or F4 (Total Life) accordingly. The cells in column A will always be numbers.

Here's what I've tried, I think I may be off the path completely based off of my single cell approach:

function workTime(input) {
  if (input.getBackground() == "#d9ead3") {
    input.setFontColor(4285f4)
  } else {
    input.setFontColor(null)
  }
}
//I get errors on line 3 for some reason though...

TL;DR Based on the background colors of the cells, how do I create a function that calculates the sum of the numbers in those specific colors and displays them in different cells under the "Total Work Time" and "Total Life Time" accordingly?

1
google-apps-scriptgoogle-sheets
Unfortunately, from your question, I cannot understand about Based on the background colors of the cells, how do I create a function that calculates the sum of the numbers in those specific colors and displays them in different cells under the "Total Work Time" and "Total Life Time" accordingly?. And, I cannot understand about your sample image. I apologize for this. Can I ask you about the sample input and output situation you expect? – Tanaike
Maybe this google sheets add-on (workspace.google.com/marketplace/app/custom_count_and_sum/…) might be of some interest to you? – JPV
@Tanaike Input = background color of cell + value (always a number); Output = sum of the values that have the same background color in a different cell. Underneath the time column there are colors highlighted. This is due to the conditional formatting from the second column, where the only options I put are either "Work" or "Life". Inside of the highlighted cells I would put a number and I want the function to take numbers in cells with a specific background color (as set by the conditional formatting) – PerplexingParadox
Thank you for replying. I noticed that an answer has already been posted. In this case, I would like to respect the existing answer. – Tanaike

1 Answers

0
votes

The "custom formula" approach is very limited

The only input you'll get into the custom formulae are the values, not the cell object. The function that is running the formula will never know about its location or formatting. It receives a value or an array of values, and returns a value or am array of values.

Apps Script version

function workTime2() {
  let file = SpreadsheetApp.getActive();
  let sheet = file.getSheetByName("Sheet1");
  let range = sheet.getRange('A1:A16');
  let targetColor = "#00ffff"
  let values = range.getValues(); // [[1],[2],[3]...]
  let colors = range.getBackgrounds(); // [[#ffffff],[#00ffff],[#ffffff]...]

  let sum = 0
  for (let i = 1; i != values.length; i++){ // starting at 1 to skip first row
    let value = values[i][0]
    let color = colors[i][0]
    if (color == targetColor) {
      sum += value
    }
  }

  let resultCell = sheet.getRange('B2');
  resultCell.setValue(sum);
}

This script will sum the values in A1:A16 if they are turquoise. Putting the sum in B2.

enter image description here

This is a way to get a sum based of a cell value. This should give you a good starting point to customize to your liking.

Reference