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

Question

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"

When loging the arr_2, values "1", "2", "1,2,3", "3" should actually be "2", "3", "2,3,4", "4" — prkos
– prkos, Commented Aug 3, 2020 at 21:39
If your shared Spreadsheet is the situation before you run the script, can I ask you about the detail of expected situation after you run the script? — Tanaike
– Tanaike, Commented Aug 3, 2020 at 23:10
Ofcourse, expected situation would be like in column C, getting every number from array_2, which matches with the number from array_1 column, incremented by 1. — prkos
– prkos, Commented Aug 4, 2020 at 5:58
@prkos Questions should be 1. Self-contained: not depend on external links- Consider adding a screenshot of your sheet. 2. Have a clear problem statement and debugging details. See minimal reproducible example Kindly edit your question. If you do, reply back and I'll add a answer. — TheMaster
– TheMaster, Commented Aug 4, 2020 at 7:20

TheMaster · Accepted Answer · 2020-08-04 17:48:26Z

1

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));

edited Aug 4, 2020 at 17:48

answered Aug 4, 2020 at 17:38

TheMaster

51.6k7 gold badges76 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

prkos Over a year ago

This is greatly appreciated and exactly what I wanted to achieve! Big thanks, apologies for a bit unclear question, but this was my first post. Thank you again!

halfer · Accepted Answer · 2023-02-10 21:33:36Z

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());
        
      }
    }
  }
}

Thanks for the answer Mateo it is very helpful and well explained, however idea was to only increment matches, lets say that there is number 6 in array_2 that one shouldn't be incremented.

Collectives™ on Stack Overflow

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

Problem:

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Problem:

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related