Looping a function in Google Sheets

0
votes

I am trying to migrate from Microsoft Excel to Google sheets in order for the company to work from any work station.

I want to compare two ranges of data, one in one column and the other in another column.

  1. If the cell value in the first range equals the cell value in the second range, then I want it to...
  2. Add the value from the second range to the first range in the matching cell and continue checking the rest of the range.

I have done this easily in VBA but I am having a hard time doing it in Goole Sheets.

For now the code in Google Sheets does the following:

If it finds a matching value between the two ranges it adds the value to the every single cell in the first range.

Here is what my code looks like:

function myFunction() {
  var pr = SpreadsheetApp.openById("my spreadsheet"); 
  var sheet = pr.getSheetByName("sheet1");
  var sheet1 = pr.getSheetByName("sheet1");
  var cell = sheet.getRange('J3:J95');
  var cell1 = sheet1.getRange('C3:C1125');
  
  if(cell1.getValue()===cell.getValue()) { 
   cell1.offset(0, 2).setValue(cell1.offset(0, 2).getValue() + cell.offset(0, 1).getValue())
  }  
}

How can I make this add value only to the one matching cell and not the entire range?

1
javascriptgoogle-apps-scriptgoogle-sheets
Your two ranges have different dimensions. Do you want to compare each cell in the first range to each of the cells in the second range, and add any that are equal? Can you provide an example of the desired output with a small range? - Aerials
Yes. For example "cell" is A1 to A5 and "cell1" is B1 to B5. I am trying to compare each cells and if A1 is equal to B2 and B5 it will add to A1 whatever is in the cells next to B2 and B5 thus the offset(0,1) in my code. What my code does is it only checks only the first cell in "cell" and only the first cell in "cell1" and if they are equal it adds whatever is in the cell next to it to the entire range (if A1 is equal to B1 then it adds the next cell to it to A1 A2 A3 A4 A5) I do not know why it does it like this or how to fix it - Peter
When you say add, are you talking about numbers or strings? - Aerials
Add values. The values of all the cells are numbers (1,2,3 or 4) - Peter

1 Answers

0
votes

You can achieve this using for-loops:

An example:

Before running script: Before running function

Script:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet(); 
  var keys = sheet.getRange('A1:A5').getValues();
  var values = sheet.getRange('B1:B5');

  for (var i = 0; i < keys.length; i++){
    isMatch(keys[i], values);
  }

}

function isMatch(key, values) {
  for(var i=0; i< values.getNumRows(); i++){
    var sample = values.getValues()[i];
    if (key[0] == sample[0]) {
      values.getCell(i+1, 1).offset(0,1).setValue(values.getCell(i+1, 1).offset(0,1).getValue()+key[0]);
    }
  }
}

After running script: enter image description here