I'm looking to set up a sheet that grabs data from Sheet 'input' and set it to Sheet 'output'. This works fine for a row that only has one only value to return. However, some rows contain more than one value that needs to be returned. For example,

The desired outcome would be to grab the value (colour) and set it to the next available row on Sheet 'output', based on the pre-set order of Sheet 'input' e.g.

Piece of code down below;

function collectData()
{  
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('input'); 
  var sOutput = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('output'); 
  var endRow = ss.getLastRow();
  var firstCell = sOutput.getRange('A1').setValue('<start>');
        
  for (var i = 1; i <= endRow; i  )
{
 //car1 
if (ss.getRange(i, 1).getValue()== 'car1' && ss.getRange(i, 2).getValue() != null )
{
 sOutput.getRange(sOutput.getLastRow()   1, 1).setValue(ss.getRange(i,2).getValue()); 
}
// car2
if else (ss.getRange(i, 1).getValue()== 'car2' && ss.getRange(i, 2).getValue() != null )
{
  sOutput.getRange(sOutput.getLastRow()   1, 1).setValue(ss.getRange(i,2).getValue()); 
}
// car3 
if else (ss.getRange(i, 3).getValue()== 'car3' && ss.getRange(i, 4).getValue() != null )
{
 sOutput.getRange(sOutput.getLastRow()   1, 1).setValue(ss.getRange(i,4).getValue()); 
}
else {}
}
}

CodePudding user response：

Here's how to get the data you want from input into output

I skipped a lot of your work with maxIndex and other stuff because I didn't want to take the time to figure it out. But hopefully this will give you an example to assist you in accomplishing what you want.

function collectDataTest() {
  const ss = SpreadsheetApp.getActive();
  const ish = ss.getSheetByName('input');
  const ishsr = 2;//where data starts
  const osh = ss.getSheetByName('output');
  osh.clear();
  const ivs = ish.getRange(ishsr,1,ish.getLastRow() - ishsr   1, ish.getLastColumn()).getValues();
  const lf = ['car1','car2','car3','car4'];
  let oA = [];
  ivs.forEach((r,i) => {
    let idx = lf.indexOf(r[0]);
    if(~idx && r[1]) {
      oA.push(r);
    }
  })
  osh.getRange(osh.getLastRow()   1, 1, oA.length, oA[0].length).setValues(oA);
}

If you wish to use the value of lf in your result you may use lf[idx] to determine which it is. If this doesn't assist you or if you wish me to delete it just let me know.

• Bitwise Not

CodePudding user response：

Turns out I was looking in the wrong place. Just had to add 'getRange' and follow it up by the required 'set.Value()' within the same rows if statement:

So initial code was:

// car2
if else (ss.getRange(i, 1).getValue()== 'car2' && ss.getRange(i, 2).getValue() != null )
{
  sOutput.getRange(sOutput.getLastRow()   1, 1).setValue(ss.getRange(i,2).getValue()); 
}
// car3 
if else (ss.getRange(i, 3).getValue()== 'car3' && ss.getRange(i, 4).getValue() != null )
{
 sOutput.getRange(sOutput.getLastRow()   1, 1).setValue(ss.getRange(i,4).getValue()); 
}

Modified code is:

// car2 & 3
    if else (ss.getRange(i, 1).getValue()== 'car2' && ss.getRange(i, 2).getValue() != null )
    {
      sOutput.getRange(sOutput.getLastRow()   1, 1).setValue(ss.getRange(i,2).getValue()); 
      sOutput.getRange(sOutput.getLastRow()   1, 1);
      sOutput.getRange(sOutput.getLastRow()   1, 1).setValue(ss.getRange(i,4).getValue()); 
    }