CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
murrietahector
murrietahector

Reputation: 11

How to move cell data so that it becomes a column header and move other cell data under it?

The best way to articulate what I am trying to do is by referring to the screenshotthis screenshot

I need to move the green cells in column A so that each one becomes a column header as shown. Additionally, I need to place any and all red cells that fall BELOW each green cell in column A under the respective column header as shown.

I was successful in writing a function to move one green cell into a column header and one red cell under it but I can't figure out how to repeat it for the rest. This is what I have tried:

function OrganizeResults(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var results = ss.getSheetByName("results");
var rowCombos = sheet.getLastRow();
var resultsRows = results.getLastRow();
var red = sheet.getRange(2,5).getBackgroundColor();
var green = sheet.getRange(2,2).getBackgroundColor()

for (i=1; i<=resultsRows; i++) {
for (n=2; n<=resultsRows; n++){

if (green == results.getRange(i,1).getBackgroundColor())
{
var row = results.getRange(i,2).getValue();
if (red == results.getRange(n,1).getBackgroundColor())
{
var redrow = results.getRange(n,2).getValue();
if (row < redrow)
{                    
   results.getRange(1,3).setBackgroundColor(green).setValue(
        results.getRange(i,1).getValue()) &&     
        results.getRange(2,3).setBackgroundColor(red).setValue(
          results.getRange(n,1).getValue());

}
}

}
}
}

Interestingly, the function places each green cell in C1 (starting with the first) then each red cell in C2 and iterates through each one thereafter. I can't get it to place them and leave them and move on to the next column.

Upvotes: 1

Views: 126

Answers (1)

Cooper
Cooper

Reputation: 64082

Before running the script

This is before running the script.

function redgreenshow()
{
  var colvA=[];
  var colcA=[];
  for(var i=0;i<10;i++) //initialized output array to keep from having to mess with that
  {
    colvA[i]=[];
    colcA[i]=[];
    for(var j=0;j<10;j++)
    {
      colvA[i][j]=' ';
      colcA[i][j]=' ';
    }
  }
  var s = '';
  var sht=SpreadsheetApp.getActiveSheet();
  var rng=sht.getDataRange();
  var rngcA=rng.getBackgrounds();
  var rngvA=rng.getValues();
  var cidx=0;
  for(var i=0;i<rngcA.length;i++)
  {
    if(rngcA[i][0]=='#00ff00')//finds the greenies
    {
      var ridx=0;
      colvA[ridx][cidx]=rngvA[i][0];//store greenie value
      colcA[ridx][cidx]=rngcA[i][0];//store greenie color
      ridx++;
      for(var j=i+1;j<rngcA.length;j++)
      {
          if(rngcA[j][0]=='#ff0000')//finds the reddies below the greenie
          {
            colvA[ridx][cidx]=rngvA[j][0];//store reddy value
            colcA[ridx][cidx]=rngcA[j][0];//store reddy color
            ridx++;
          }

        }
        s+='colvA[' + ridx + '][' + cidx + ']=' + colvA[ridx][cidx] + '<br />colcA[' + ridx + '][' + cidx + ']=' + colcA[ridx][cidx] + '<br />';
        cidx++;
      }
    }
    var outrng = sht.getRange(1, 3, 10, 10);//10 x 10 because I initialize that way.  
    outrng.setValues(colvA);//set the values
    outrng.setBackgrounds(colcA);//set the colors

}

After running the script.

After running the script

Upvotes: 0

Related Questions