I Have an array from a two columns concatenated in column

Here is the link to spreadsheet - https://docs.google.com/spreadsheets/d/180iMihBq9-Gep9Em6v570pshPRNgsruaboFv5e4XHes/edit#gid=1082081384

var a1_range = To_check.getRange("A2:A");
var a1_val = a1_range.getValues();

I am trying to read the values of whole column and take out first and second elements into two different arrays correspondingly.

• If in the concatenated elements there is "Included" then it should skip it
• If in the concatenated elements there is "Flat Mon" then it should skip it
• the loop should break after encountering concatenation length as 0

I have tried this code below

a1_arr= [];
  for (var j in a1_val){
    if(a1_val[j][0].split("-").length==2){
      a1_arr.push(BillFUOM_val[j][0]);
    }
    else{ 
      break;}
  }
  
  for (var j in a1_arr){
    var check_inluded = a1_arr[j].includes("Included");
    if(check_inluded==true){
      a1_arr.splice(j);
    }
  }
for (var j in a1_arr){
    var check_flat = a1_arr[j].includes("Flat Mon");
    if(check_flat==true){
      a1_arr.splice(j);
    }
  }

  var a1_arr_split = a1_arr.toString().split("-");
  var a1 = [];
  a1_arr_split.forEach(function(q){a1.push([q]);});
  Logger.log(a1);

I want array a1 to be

[GGG,Comms  Excess]

and an array a2 to have the above arrays corresponding second item

[Yearly,Monthly]

Please help! @Tanaike - san

CodePudding user response：

Fill in the conditions and it should be good to go.

const ssid = '180iMihBq9-Gep9Em6v570pshPRNgsruaboFv5e4XHes';
const sss = SpreadsheetApp.openById(ssid);
const range = sss.getRange('A1:A');

function results() {
  const values = range.getValues();

  const output = {arr1: [],arr2: []};
  for (const row of values) {
    // if you want to break the loop where the concatenation length is 0, you should not use 'A1:A' as range at the very begining,
    // and since you use 'A1:A' as you range, as long as your data do not have infinity length,
    // there is not much point to iterate the array just to find out the length so as to let you break it at the right place,
    // instead, you can just skip a loop with continue if it is empty.
    if (row[0] = '') continue; // If there is no value, skip it.
    
    const str = row[0];
    if (str.includes('Included')) continue; // If in the concatenated elements there is "Included" then it should skip it
    if (str.includes('Flat Mon')) continue; // If in the concatenated elements there is "Flat Mon" then it should skip it
    if (str = '-') continue; // If the value is a single "-", skip it

    // I can't understand what is the result structure you want, but you can do this to push the result into the output object:
    const condition_1 = '1st condition you want to check';
    const condition_2 = '2nd condition you want to check';
    if (condition_1) output.arr1.push(str);
    if (condition_2) output.arr2.push(str);
  }

  console.log(output);
}