CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
prkos
prkos

Reputation: 13

How to increment each string element from one array that is found in another array?

I've kind of stuck on this problem, the goal is to find matching values from column A in column B and increment same values by 1 only in column B, this goes for single aswell as many character strings. Any help would be much appreciated, thanks! This is what I tried:

function compareValuesAndIncrementByOne(){
  
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName("Data");
  
  var range = sh.getDataRange();
  var values = range.getValues();
  
  var headers = values.shift();
  
  var array_1 = [];
  var array_2 = [];
  
  for (var i = 0; i < values.length; i++){
    
    array_1.push([values[i][0]]);
    array_2.push([values[i][1]]);    
    
    array_2[i].join().split(',');
    
  }
  try{
  for (var i = 0; i < array_1.length; i++){
    if (array_1[i].includes(array_2[i])){
      var index = array_2[i].findIndex(array_1[i]);
      array_2[index] = parseInt(array_1[i])+1;
    }
  }}catch(e){Logger.log(e)}
  Logger.log(array_2);
}

Here is the link to the spreadsheet: https://docs.google.com/spreadsheets/d/1u55xVnGrZfaHedB1UwhQpcuxmtVtkWi-LxDHtU30CwQ/edit?usp=sharing

Desired Result Screenshot

Problem:

When loging the arr_2, values are "1", "2", "1,2,3", "3", but they should actually be "2", "3", "2,3,4", "4"

Upvotes: 1

Views: 173

Answers (2)

Mateo Randwolf
Mateo Randwolf

Reputation: 2930

If I understood right your question you want a function that takes the values in column B and adds +1 to them no matter if they are of type number or a string of the form 1,2,3 , i.e a string of numbers separated by commas.

The following piece of code with explanatory comments achieves this purpose. The image below is a demonstration of this.

function compareValuesAndIncrementByOne(){
  // Get our sheet
  var sheet = SpreadsheetApp.getActive().getSheetByName('Data');
  
  // Get the range of column B with all the values we need to increment. As getValues()
  // return a 2D array we flat it to ease the manipullation of it
  var valuesRangeB = sheet.getRange(2,2,sheet.getLastRow(),1).getValues().flat();
  
  // for all the rows of the column
  for(i=0;i<valuesRangeB.length;i++){
    // if the row is not empty
    if(valuesRangeB[i]!==''){
      // if the row values is not a string, i.e it is a number we simply need to add 1
      // to the value obtained by get values
      if(valuesRangeB[i].length == null){
        sheet.getRange(i+2, 4).setValue(valuesRangeB[i]+1);
        // else in our case it would be a string such as 1,2,3
      }else{
        // convert string to an array by splitting it by commas. "1,2,3" would then be
        // [1,2,3] array object. Then map all its elements to add 1 to each of the elements
        // of the array we got from the string and convert it back to a string which will 
        // automatically add the commas back 
        var arr = valuesRangeB[i].split(",");
        arr = arr.map(function(val){return ++val;});
        sheet.getRange(i+2, 4).setValue(arr.toString());
        
      }
    }
  }
}

enter image description here

Upvotes: 0

TheMaster
TheMaster

Reputation: 50445

Use Array.map with recursion:

const array_1 = [[1], [2], [3]], //simulate array_1 and 2
  array_2 = [[1], [5], [3], ['1,3,2,4']];
const array_1_flat = array_1.flat(); //flatten array to use includes
const increment = arg =>
  Array.isArray(arg) //if the arg is a array, recurse each element with map
    ? arg.map(increment)
    : String(arg).includes(',') //if arg includes "," , split and recurse
    ? arg
        .split(',')
        .map(e => increment(parseInt(e)))
        .join()
    : array_1_flat.includes(arg) //if arg is present in array_1
    ? arg + 1
    : arg;
console.log(array_2.map(increment));

Upvotes: 1

Related Questions