Z
Z
ZorX2019-12-18 22:15:50
Google Apps Script
ZorX, 2019-12-18 22:15:50

How to add a Google Apps script?

Guys, hello!
Help write a script.
I'm not a programmer, but after smoking Google, I managed to do this

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var code1 = ss.getActiveSheet().getRange("A3").getValue();
  var code2 = ss.getActiveSheet().getRange("M4").getValue();
  var value1 = ss.getActiveSheet().getRange("E3").getValue();
  var value2 = ss.getActiveSheet().getRange("N4").getValue();
  
   if (code1==code2){
    var sum = value1 + value2;
     var rr = ss.getActiveSheet().getRange("E3").setValue(sum);
  }
   
 
}

Everything that is written here is done for one cell.
It checks cell M4, if this cell matches A3, then it sums E3 and N4
A3 - this is the product code in the table
M4 - this is the product code that I enter manually
E3 - the amount of goods in the warehouse
N4 - the amount of goods that arrived and I enter it manually .
Actually, as I said above, it all works with one cell.
But I need it to work for each cell separately. For example, I enter the code "2345" it found this cell, for example it will be A45 and after it found it, but E45 contributed the sum of E45 and N4.

Answer the question

In order to leave comments, you need to log in

1 answer(s)
G
Grigory Boev, 2019-12-19
@ZorX

Getting data for one cell is sooooooo long. It is better to take all the data at once in the desired range, like this:
And then you can access this data by row number and column number, not forgetting that the countdown starts from zero:
You also need to write down in bulk. As a result, something like this will turn out:

function onOpen() { 
  //Выполняется при открытии
  SpreadsheetApp
  .getUi()
  .createMenu('Меню')
  .addItem('Выполнить','doIt')
  .addToUi();
};

function doIt() {
  try{
    var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); //Текущий лист
    
    var data1 = ss.getRange("A3:E" + ss.getMaxRows()).getValues();
    var data2 = ss
    .getRange("m4:n" + ss.getMaxRows())
    .getValues()
    .filter(function(row) // Убираем пустые строки
            {
              return row[0]!="";
            }
           );
    
    for (var row2=0;row2<data2.length;row2++){
      for (var row1=0;row1<data1.length;row1++){
        if (data1[row1][0]===data2[row2][0]){
          data1[row1][4]+=data2[row2][1];
          break; //Если значение найдено, дальше не ищем
        };
      };
    };
    
    ss.getRange("A3:E" + ss.getMaxRows()).setValues(data1); //Вывод данных
    SpreadsheetApp.getActive().toast("Готово!");
  }catch(e){ //Если вдруг ошибка
    Logger.log("Ошибка! " + e);
  };
}

Demo table

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question