Google Sheets Custom Script iterate through a range and print values in a determined order

Question

UPDATE WITH a-change 's response and code

I am working on a function that will let me select a range in a sheet in Google Sheets and then paste the values that I am interested in into a specific order on another sheet.

Suppose RawData (Sheet1) looks like this:

I want to grab the range RawData!A1:L15, so basically everything that is that picture.

Afterwards I want to print it in another sheet (Sheet2 called Analysis) like so:

So far this is the code:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("RawData");
  var targetSheet = ss.getSheetByName("Analysis");
  var range = sheet.getDataRange();
  var values = range.getValues();
  for (var i in values) {
    var row = values[i];
    var column = 1;
    for (var j in row) {
      var value = row[j];
      if (value !== "X") {
        targetSheet.getRange(parseInt(i) + 1, column).setValue(value);
        column++;
      }
    }
  }
}

This code results in values being pasted in the 'Analysis' with the same order as in the 'RawData' sheet. The idea is for the data to be able to be pasted in a trio format, with no spaces between values. So the first trio would be: A1 = 1, B1 = 2, C1 = 3, A2 = 4, B2 = 5, C2 = 6, and so on.

Firstly, determine what is the expected output. `row` gets single values between 0 and 14 but you select a range of 24 cells. How you except to put 14 numbers in a 24 cells? Secondly, like I said in my deleted answer, setValue accepts a single element but you are passing an array, this is why you get 0 in every cell of the selected range. — Marios, Nov 07 '20 at 17:26
The expected output is stated in the problem. I don't understand why the getValues is only getting 0 to 14 values when I have selected the range A1:L15. I tried answering in the deleted answer that when I try your resolution that it pops the error message: "Exception: The parameters (number[]) don't match the method signature for SpreadsheetApp.Range.setValues. (line 10, file "Code")" And I think this is because the getValues transforms everything into a single array and setValues accepts 2D Arrays. — Pablo Rodriguez, Nov 07 '20 at 17:30
Because the array has a length of 15 and the array index in javascript starts with 0, therefore it goes from 0 to 14. getValues gives you a 2D array. Read the official documentation for more information. — Marios, Nov 07 '20 at 17:47

a-change · Accepted Answer · 2020-11-07T19:40:07.783

A couple of things:

for (var row in values) { — here row is an index of an element, not the element itself. So it'll always be not equal to "X". Better to put it this way:

for (var i in values) {
  var row = values[i];
}

Then you need to iterate over row to get to a single element and compare it with "X":

for (var i in values) {
    var row = values[i];
    for (var j in row) {
        var value = row[j];
        if (value !== "X") {

        }
    }
}

Next thing is pasting the value to your target sheet. The reason you are getting the same number in all the cells is that you're calling setValue on the whole A1:C8 cells range instead of one particular cell.

for (var i in values) {
    var row = values[i];
    var column = 1;
    for (var j in row) {
        var value = row[j];
        if (value !== "X") {
             targetSheet.getRange(parseInt(i + 1), column).setValue(value);
             column++;
        }
    }
}

targetSheet.getRange(i, j) here gives you a single-cell precision.

So alltogether your code would look something like:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("RawData");
  var targetSheet = ss.getSheetByName("Analysis");
  var range = sheet.getDataRange();
  var values = range.getValues();
  for (var i in values) {
    var row = values[i];
    var column = 1;
    for (var j in row) {
      var value = row[j];
      if (value !== "X") {
        targetSheet.getRange(parseInt(i) + 1, column).setValue(value);
        column++;
      }
    }
  }
}

See how the target sheet is set as a variable instead of using a range on the source sheet — it gives you more readability and freedom
It seems that when iterating like for (var i in row) i is considered to be a string so the parseInt call
column variable is needed to make sure there are no empty cells in the target sheet
I've also changed sheet.getRange(1,1,15) to sheet.getDataRange() to make sure your code gets all the data in the sheet
The approach of setting values into single cells separately is not optimal. It should work for you in your case as the data range seems pretty small but as soon as you get to hundreds and thousand of rows, you'll need to switch to setValues, so you'll need to build a 2D-array before pasting the values. The tricky thing is that your resulting rows may have a variable number of items (depending on how many Xs are in a row) while setValues expects all the rows to be of the same length — it's possible to get round it of course.

Just a note, setting variables in a loop is not efficient. However, the OP hasn't clarified what he wants so I assume that's enough. — Marios, Nov 07 '20 at 19:43
Thank you very much, you helped me understand quite a bit better how I should think the problem. I've tried the code and it works well, but it doesn't solve the issue of being able to read the element of the array and after every third one break to a separate row, as well as it takes the numerical numbers and pastes them in Analysis as they are registered in RawData. The format in RawData is for, as you said, hundreds of rows and I need to have the data registered as a trio. Is there any workaround for this specific need? — Pablo Rodriguez, Nov 08 '20 at 04:29

Google Sheets Custom Script iterate through a range and print values in a determined order

1 Answers1