Tips for generating arrays using cell data from Google Sheets

Question

Tips for generating arrays using cell data from Google Sheets

I am currently working on extracting information from a Google Sheet to create a list of (4X1) arrays.

   A        B    C      D       E     F      G      H      I     J 
Project | Per1 | W1 |  Team1 | Per2 | W2 | Team2 | Per3 | W3 | Team3|
—————————————————————————————————————————————————————————————————————
   p1   | Bill | .5 |  Tech  | Alice|  1 | Other |      |    |      |
   p2   |Larry |  1 |  Tech  | Bill |  1 | Other | Tina | 1  | Other|
   p3   | Joe  |  2 |  Tech  | Beth |  1 | Tech  |      |    |      |
   p4   |Kathy | .5 |  Tech  |      |    |       |      |    |      |
   p5   | Bill | 1  |  Tech  | Larry|  1 | Other |      |    |      |

*Due to constraints, I am unable to post images at the moment.

Currently, my script allows me to generate a list of (3x1) arrays:

 function arrayOfObjects() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Sheet1');
  var vA=sh.getRange("B2:D6").getValues();
  Logger.log(vA);
}

The current results are as follows:

 [[Bill, 0.5, Tech], [Larry, 1.0, Other], [Joe, 2.0, Tech], [Kathy, 0.5, Other], [Alice, 1.0, Tech]]

However, this only covers the range B2:D6

I am looking to establish links between names and projects (p1,p2,... for further analysis). I aim for the final output to be in the form of (4X1) arrays:

[[p1,Bill,.5,Tech], [p1,Alice,1,Other],[p2,Larry,1,Other],[p2,Bill,.5,Other],
[p2,Tina,1,Other]........[p5,Larry,1,Other]]

Eventually, I want to present the data in table format:

    A           B                         
| Name | Number Of project|  
———————————————————————————
| Bill |        3         |
|Larry |        2         |
| Joe  |        1         |   
|Kathy |        1         |
| Alice|        1         |
   etc...

javascript arrays sorting google-apps-script google-sheets

Answer 1

Answer №1

Exploring 1x4 Arrays and Objects

In this tutorial, you will learn how to generate 1x4 arrays and objects. It's up to you to choose which one fits your needs.

function extractProjectInfo() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Projects');
  var rg=sh.getDataRange();
  var vA=rg.getValues();
  var projArray=[];
  var projObject=[];
  for(var i=1;i<vA.length;i++) {
    for(var j=1;j<vA[i].length;j+=3) {
      if(vA[i][j] && vA[i][j+1] && vA[i][j+2]) {
        projArray.push([vA[i][0],vA[i][j],vA[i][j+1],vA[i][j+2]]);
        projObject.push({project:vA[i][0],person:vA[i][j],weight:vA[i][j+1],team:vA[i][j+2]}); 
      }
    }
  }
  var resultObj={array:projArray,object:projObject};
  return resultObj;
}

This version allows you to view them in a dialog box and demonstrates how you can access them using either indices or keys.

function displayProjectInfo() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Projects');
  var rg=sh.getDataRange();
  var vA=rg.getValues();
  var projArray=[];
  var projObject=[];
  for(var i=1;i<vA.length;i++) {
    for(var j=1;j<vA[i].length;j+=3) {
      if(vA[i][j] && vA[i][j+1] && vA[i][j+2]) {
        projArray.push([vA[i][0],vA[i][j],vA[i][j+1],vA[i][j+2]]);
        projObject.push({project:vA[i][0],person:vA[i][j],weight:vA[i][j+1],team:vA[i][j+2]}); 
      }
    }
  }
  var resultObj={array:projArray,object:projObject};
  var html='<style>td,th{border:1px solid black;text-align:center;}</style><table>';
  html+='<tr><th>Project</th><th>Person</th><th>Weight</th><th>Team</th></tr>';
  html+='<tr><td colspan="4">Array(indices)</tr>';
  for(var i=0;i<projArray.length;i++) {
    html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td>',projArray[i][0],projArray[i][1],projArray[i][2],projArray[i][3]);
  }
  html+='<tr><td colspan="4">Object(key,value)</tr>';
  for(var i=0;i<projObject.length;i++) {
    html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td>',projObject[i].project,projObject[i].person,projObject[i].weight,projObject[i].team);
  }
  html+='</table>';
  var userInterface=HtmlService.createHtmlOutput(html);
  SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Array Assemble - Array on Top and Object on Bottom')
}

Answer 2

Exploring 1x4 Arrays and Objects

In this tutorial, you will learn how to generate 1x4 arrays and objects. It's up to you to choose which one fits your needs.

function extractProjectInfo() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Projects');
  var rg=sh.getDataRange();
  var vA=rg.getValues();
  var projArray=[];
  var projObject=[];
  for(var i=1;i<vA.length;i++) {
    for(var j=1;j<vA[i].length;j+=3) {
      if(vA[i][j] && vA[i][j+1] && vA[i][j+2]) {
        projArray.push([vA[i][0],vA[i][j],vA[i][j+1],vA[i][j+2]]);
        projObject.push({project:vA[i][0],person:vA[i][j],weight:vA[i][j+1],team:vA[i][j+2]}); 
      }
    }
  }
  var resultObj={array:projArray,object:projObject};
  return resultObj;
}

This version allows you to view them in a dialog box and demonstrates how you can access them using either indices or keys.

function displayProjectInfo() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Projects');
  var rg=sh.getDataRange();
  var vA=rg.getValues();
  var projArray=[];
  var projObject=[];
  for(var i=1;i<vA.length;i++) {
    for(var j=1;j<vA[i].length;j+=3) {
      if(vA[i][j] && vA[i][j+1] && vA[i][j+2]) {
        projArray.push([vA[i][0],vA[i][j],vA[i][j+1],vA[i][j+2]]);
        projObject.push({project:vA[i][0],person:vA[i][j],weight:vA[i][j+1],team:vA[i][j+2]}); 
      }
    }
  }
  var resultObj={array:projArray,object:projObject};
  var html='<style>td,th{border:1px solid black;text-align:center;}</style><table>';
  html+='<tr><th>Project</th><th>Person</th><th>Weight</th><th>Team</th></tr>';
  html+='<tr><td colspan="4">Array(indices)</tr>';
  for(var i=0;i<projArray.length;i++) {
    html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td>',projArray[i][0],projArray[i][1],projArray[i][2],projArray[i][3]);
  }
  html+='<tr><td colspan="4">Object(key,value)</tr>';
  for(var i=0;i<projObject.length;i++) {
    html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td>',projObject[i].project,projObject[i].person,projObject[i].weight,projObject[i].team);
  }
  html+='</table>';
  var userInterface=HtmlService.createHtmlOutput(html);
  SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Array Assemble - Array on Top and Object on Bottom')
}

Answer 3

Answer №2

Do you have to create an entry for each team member row by row?

data = vA; 
for (var i=0; i<data.length;i++)
    {
    var project = data[i][0];
    if (data[i][1]!="")push([project,data[i][1],data[i][2],data[i][3] ]);//first person
    if (data[i][4]!="")push([project,data[i][4],data[i][5],data[i][6] ]);//second person
    if (data[i][7]!="")push([project,data[i][7],data[i][8],data[i][9] ]);//third person
    }//for loop

There might be a more graceful approach, but this is my initial idea.

Answer 4

Do you have to create an entry for each team member row by row?

data = vA; 
for (var i=0; i<data.length;i++)
    {
    var project = data[i][0];
    if (data[i][1]!="")push([project,data[i][1],data[i][2],data[i][3] ]);//first person
    if (data[i][4]!="")push([project,data[i][4],data[i][5],data[i][6] ]);//second person
    if (data[i][7]!="")push([project,data[i][7],data[i][8],data[i][9] ]);//third person
    }//for loop

There might be a more graceful approach, but this is my initial idea.

Tips for generating arrays using cell data from Google Sheets

Answer №1

Answer №2

Similar questions

Vue.js does not have the ability to toggle a font-awesome icon

unusual actions when decoding JSON data

What could be causing my Vue component to not refresh?

What is the best way to determine the number of characters that will fit within the width of the document?

Saving the ending reference to an array that has not been initialized within a compile-time context

How can I retrieve information from an object using Mongoose queries?

Ways to trigger an onClick event multiple times in React

Enhance your website's functionality with the dynamic combination of Bootstrap

Problem with Angular: ng-show not constantly re-evaluating expression

The error message "Cannot call expressjs listen on socket.ip" indicates that there

(node:2824) UnhandledPromiseRejectionWarning: ReferenceError: The variable "role" has not been declared

Change the color when hovering over the select box

Slick Slider fails to load on web browsers

Turn off the scrolling bars and only allow scrolling using the mouse wheel or touch scrolling

How is it possible to retrieve a list of ID fields from an array of hash references in Perl?

javascriptDOM selectors document object

Merge JavaScript function mouseup

What is the best way to retrieve a variable's value using its reference?

AngularJS integration with Google OAuth

I encountered a problem while trying to incorporate the solution that prompts another button click event