Grouping arrays with different lengths to make a single sentValues() for the spreadsheet

Posted on


Currently I find out which is the biggest array and from it I generate a while loop and I go around errors with try catch:

function stackoverflow() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Ratings Time A');
  var menu = [
    ['Atletico Lanus'],
    ['Atletico Lanus'],
    ['Independiente Jose Teran'],
    ['2.83 Fair'],
    ['2.77 Fair']

  //Home Team
  var home_team = [
    ['Fernando Monetti (GK)', '53'],
    ['Braian Aguirre (DF)', '60'],
    ['Nicolas Pasquini (DF)', '57'],
    ['Matias Pérez (DF)', '43'],
    ['Diego Braghieri (DF)', '47'],
    ['M. Gonzalez (MF)', '23'],
    ['Raul Loaiza (MF)', '41'],
    ['Tomas Belmonte (MF)', '62'],
    ['Lautaro Acosta (ST)', '63'],
    ['José Sand (ST)', '52']

  // Away Team
  var away_team = [
    ['Moisés Ramirez (GK)', '61'],
    ['Richard Schunke (DF)', '68'],
    ['Luis Segovia (DF)', '65'],
    ['Willian Vargas (DF)', '57'],
    ['Mateo Carabajal (DF)', '57'],
    ['Jhoanner Chavez (DF)', '52'],
    ['Fernando Gaibor (MF)', '60'],
    ['Junior Sornoza (MF)', '68'],
    ['Lorenzo Faravelli (MF)', '69'],
    ['Jonathan Bauman (ST)', '73'],
    ['Jaime Ayovi (ST)', '60'],
    ['Joan Lopez (GK)', '19']

  final_array = [];
  let i = 0;
  let ct = Math.max(menu.length, home_team.length, away_team.length);
  while (i < ct) {
    try {
      var menu_0 = menu[i];
    } catch {
      var menu_0 = '';
    try {
      var home_team_0 = home_team[i][0];
      var home_team_1 = home_team[i][1];
    } catch {
      var home_team_0 = '';
      var home_team_1 = '';
    try {
      var away_team_0 = away_team[i][0];
      var away_team_1 = away_team[i][1];
    } catch {
      var away_team_0 = '';
      var away_team_1 = '';

  sheet.getRange(3, 1, final_array.length, final_array[0].length).setValues(final_array);

This setValues() to the spreadsheet is done to generate this result (note that there is an empty column between home_team and away_team:

enter image description here

I would like a review on this method that I am using to be able to group the lists in order to generate the correct sequence in the array to define in which column each index will be sent.


  • Use const and let as needed instead of declaring all variables as var. This helps the javascript engine optimize the memory and processing of such variables

  • Practice DRY(Don’t Repeat Yourself) principle. You repeat try...catch thrice. You can create a function instead and pass different variables like menu_0 to it.

  • In this case however, you can

    • Create a collection array( [menu, home_team, [], away_team];) with the order of arrays you want concatenated.
    • Create a output array with maximum length of all those arrays(Array(Math.max( => a.length))))
    • Use and Array.flatMap to concatenate inner arrays from collection

Leave a Reply

Your email address will not be published. Required fields are marked *