Appending tables to Google Docs from Spreadsheets using Apps Script

0
votes

I have been learning to use Google Apps Scripts. One of the tasks which I have to do for my assignment is appending tables to Docs by taking data from Google Spreadsheets. I managed to simply copy a data range and create a table from it, but now I need to use some conditions, for example copy only those rows that contain a specific value. Where could I search for possible solutions of this problem? Can't find anything similar on here or elsewhere on the internet...

1
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-docs

1 Answers

2
votes

Since the return of .getValues() is two-dimensional array of values, you can use JavaScript's Array.filter() method.

Example:

In my sheet, I want to filter all person playing Basketball.

Sheets:

Sheets

Apps Script:

function myFunction() {
  //retrieve data from Sheet
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var range = sheet.getDataRange().getValues();

  var filteredData = range.filter(function(item){
    //return if the 3rd column("Sports") is equal to "Basketball"
    return item[2] == "Basketball";
  });
  //filteredData : [[John, 11.0, Basketball], [Robin, 9.0, Basketball]]

  //Append the header data (Name, Age, Sports) to the beginning of the array
  filteredData.unshift(range[0])

  //filteredData : [[Name, Age, Sports], [John, 11.0, Basketball], [Robin, 9.0, Basketball]]

  //Create document
  var doc = DocumentApp.create('Example');
  var body = doc.getBody();
  //Create table to Docs and use the filteredData as values.
  var table = body.appendTable(filteredData);
  //Set the header to bold.
  table.getRow(0).editAsText().setBold(true);
}

Docs:

Docs

References:

Array.filter()