Writing a single element of an array to another sheet

Question

I am trying to all the data from a range in a spreadsheet and filter it based on a certain condition where item[27] and item [29] equals "CO". It's working fine and filtering the range properly however I am trying to output only one item in the array which is item[3] and it's not doing that. It's giving me the filtered version of the entire range which is good but I don't want that.

This is basically a spreadsheet containing students and information about the work modules they have completed. I want to filter only the names of the students which is item[3] who have a "CO" or "complete" marked against a certain work module into another sheet. I tried to map the filtered data in another array using data.map and then tried to output just one element of the array and that is not working as well.

BTW this is my first time coding anything and using Google Apps script. I am really interested in this stuff and hopefully, one day be half as good as anyone here. Any help will me much appreciated.

function ReviewReport() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var mastersheet = ss.getSheetByName("Master Sheet 18-19-20");
  var newtab = ss.getSheetByName("ReviewReportTest(Amith)");

  var totalcolumns = mastersheet.getLastColumn();
  var totalrows = mastersheet.getLastRow();

  var masterdata = mastersheet
    .getRange(4, 1, totalrows - 3, totalcolumns)
    .getDisplayValues();

  var data = masterdata.filter(function (item) {
    //  return item[27] === "CO" && item [29] === "CO";

    if (item[27] === "CO" && item[29] === "CO") {
      return item[3];
    }
  });

  //tried to map the filtered data into another array below but this is not working as well.
  var bsb = data.map(row);
  function row(item) {
    return item[3];
  }

  newtab.clearContents();
  newtab.getRange(1, 1, data.length, data[0].length).setValues(data);
}

You never use the `bsb` variable. Did you just forget to put `setValues(bsb)` instead of `setValues(data)` ? — dwmorrin, Aug 24 '20 at 11:44
@dwmorrin I was trying to output the data array into the `bsb` array to see if it works but it did not. The `bsb` here is not in play. I should have commented it out. — amith, Aug 25 '20 at 01:17

Rubén · Accepted Answer · 2020-08-24T17:07:05.767

1

To write a single value from an Array object use Class Range setValue(value) where value could be data[rowIndex][columnIdx].

bsb was declared but not used later.
The code is adding the whole filtered data to the spreadsheet
The Array.prototype.map and it's callback are declared inside the same scope but map is used before the callback declaration. I think that it's better to declare it before calling it, or declare the callback global scope, but this is completely up to you. Think about style, readability and maintainability.

Resource

https://developers.google.com/apps-script/guides/sheets

answered Aug 24 '20 at 16:13

Rubén

34,714
9
70
166

*it should be declared before it's called.* Don't think that's necessary in this case, as OP uses `function` to declare and not `const`/`let` – TheMaster Aug 24 '20 at 16:17
@TheMaster It should be because `row` is declared in the `ReviewReport` scope instead of being declared in the global scope – Rubén Aug 24 '20 at 16:24
1

I don't think your edit is correct. The declaration is not needed to be in it's parent scope. Same scope is enough. See [hoisting](https://developer.mozilla.org/en-US/docs/Glossary/Hoisting#Learn_more) – TheMaster Aug 24 '20 at 16:32
@TheMaster Hoisting works the same way on the old and the new runtime? – Rubén Aug 24 '20 at 16:36
1

I think so. This is how js has run from the beginning, AFAIK. – TheMaster Aug 24 '20 at 16:39
1

@TheMaster I don't remember exactly the source of that, maybe from a style/troubleshooting guide. Anyway, thanks for the feedback. – Rubén Aug 24 '20 at 16:47
1

@Rubén thanks, `setValue(value)' and `data[rowIndex][columnIdx]` is working. – amith Aug 25 '20 at 01:21
1

The `bsb` array was just something I was trying and not really using. I should have commented it out. Sorry about the confusion. I put the following in a for loop and its working perfectly. Its outputting just the column I need from the filtered data. `newtab.getRange(i,1).setValue(data[i-1][3]);` – amith Aug 25 '20 at 01:29

Writing a single element of an array to another sheet

1 Answers1