Google Sheets Get cell value and column header value if cell background color is true and join them as string

2
votes

this is my first post here, so sorry if my question will be unclear.

I want to, using Google Sheets, send personalized emails but first I need to create a script. Below is an empty example of my table.

I need that this spreadsheet checks if there are cells with a yellow background in the row. If this condition is true, pick value of the header in the column where is a yellow cell and put this value into a variable as a string. Then pick value of this yellow cell and join this value to the same variable.

Same situation for every row with yellow cell

Empty example

Example: I want to check this condition for Mike Doe row. There are two yellow cells F2 and G2. First, if it's found an F2 cell, should pick header value (04.2017) and join it to the variable as a string. Then should pick the value of the cell F2 (50) and also join it the same variable. For now, our variable has value = "04.2017 - 50". Next should check another yellow cell (G2) in the same row and do exactly same thing. So at the end of this row, the variable should have value = "04.2017 - 50, 05.2017 - 50". When script finish checking this row, should put the whole value of this variable at the Message cell.

Below is shared an empty example.

https://docs.google.com/spreadsheets/d/1ZmVaEw6QS0V1A401U9l6wi2ppWfylKU74eTK4bxZlOU/edit?usp=sharing

I hope, that everything is clear. I'll be very grateful is someone help me.

1
google-apps-scriptgoogle-sheets
Are they colored via conditional formatting or manually? – Robin Gertenbach
Manually. When I manually change the color I want that script to check this and begin the whole procedure. – Marek Kolo

1 Answers

1
votes

All right so here goes my first answer on StackOverflow.. hope it helps =)

Note that below will only work if the backgrounds are the very exactly same yellow #ffff00.. I would suggest you don't use colors as a condition for an event to happen, but below will work for you.. 

function myFunction() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('Arkusz1');
  var range = sheet.getDataRange();
  var formatting = range.getBackgrounds();
  var data = range.getValues();
   for(var y = 1; y < data.length; ++y){
    var message = '';
    for(var x = 0; x < data[y].length; ++x){
      if(formatting[y][x] == '#ffff00'){
       /*/
       Because your header contains a Date Object, it will show as for example Sat Apr 01 2017 00:00:00 GMT+0200 (CEST) 
       I Guess you want it to show exactly as it shows in your spreadsheet? Then the below two lines will do:
       /*/
       var header = new Date(data[0][x]);
       header = header.getMonth() + 1 + '.' + header.getFullYear();
       message += 'Header: ' + header + ', Value: ' + data[y][x] + ' ';
      };
     data[y][7] = message
    }
  }
  range.setValues(data);
}