CODEWITHSUNDEEP
arraysgoogle-apps-scriptgoogle-sheets
allemandi
allemandi

Reputation: 79

How to join array items into single string per row in Google Apps Script?

I am wanting to envision the below in Google Sheets, where I can run a script to check Column A against three lists, and the output in Column G will state from what lists the values in Column A appeared in:

Ideal Result

I am already able to produce this effect via a very inefficient set of nested loops, setValue(), and getValue().

However, I'm aware that there are more optimal ways of going about it, such as appending items via arrays.

I am not very familiar with the syntax of Google Apps Script arrays, so I'd like guidance on how to go about this. I've written the below so far:

function list_function()
{
  var sheet = SpreadsheetApp.getActiveSheet();
  var check_list = sheet.getRange("A1:A").getValues();
  var list_A = sheet.getRange("C1:C").getValues();
  var list_B = sheet.getRange("D1:D").getValues();
  var list_C = sheet.getRange("E1:E").getValues();
  
  var checking_array = [];
  
  for(var i=1; i<check_list.length; i++)

  {

//list A here
for (var j = 1; j < list_A.length; j++)
{
  if (check_list[i][0] != "" && check_list[i][0].toUpperCase() == list_B[j][0].toUpperCase())
  {
  
    checking_array.push(['List_A"']);
  }
}

//List B here
   for (var k = 1; k < list_B.length; k++)
{
  if (check_list[i][0] != "" && check_list[i][0].toUpperCase() == list_B[k][0].toUpperCase())
  {
    checking_array.push(['List_B"']);
  }
}

  //List C here
   for (var l = 1; l < list_C.length; l++)
{
  if (check_list[i][0] != "" && check_list[i][0].toUpperCase() == list_C[l][0].toUpperCase())
  {
    checking_array.push(['List_C"']);
  }
}
//Problem here, not sure how to join() array items into single string value for cell and match according to list 
//ideally matches the above picture
    sheet.getRange(1, 7, check_list.length).setValues(checking_array);
  }
}

Upvotes: 0

Views: 1332

Answers (1)

Scot May
Scot May

Reputation: 121

You can grab the values of the entire spreadsheet at once, and get values or set values:

function list_function()
{
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  var lastColumn = sheet.getLastColumn();
  var data = sheet.getRange(1, 1, lastRow, lastColumn).getValues(); //getRange(starting Row, starting column, number of rows, number of columns)
  
 
  var check = "";

  var dataValue;
  var checkValue;
     
//All lists here
    for (var i = 1; i < lastRow; i++)  { 
           checkValue =  data[i][0];
           for (var j = 2; j < lastColumn - 2; j++)
           {
             for (var k=1 ; k< lastRow;  k++){
               dataValue =  data[k][j];
               if (dataValue.toUpperCase() === checkValue.toUpperCase() )
                {
                   check=check + "," + data[0][j];
                }
             }
             
           } 
           data[i][lastColumn-1]=check.slice(1);
           check = "";
     } 
    
    
   check = "x"; // set breakpoint here to check data[][] values before write
   sheet.getRange(1, 1, lastRow, lastColumn).setValues(data);
  
}

Upvotes: 1

Related Questions