M
M
msnn2020-11-22 23:00:25
Google Apps Script
msnn, 2020-11-22 23:00:25

How can I find rows in a Google spreadsheet that match a condition, get their row numbers, and execute my script for each?

Hi friends.
I ran into a problem that I can not solve on my own and I turn to you for help. The task is to check all the cells in the column and find those whose status does not correspond to the "Ban" value.
5fbac30aaa307899660099.jpeg
Then get the line number for each cell with the desired status and execute my script for them one by one. Now this script receives data from various columns of the active (selected) row, sends a GET request and writes the data received in response to other columns of the active row. I need the same thing, only in automatic mode. But I don't know how to write a function that will iterate over the entire array of data from the range, get the line numbers that match the condition and run the script on them.
Here is my script code:

var ss = SpreadsheetApp.getActiveSpreadsheet();
  var activeCell = ss.getActiveSheet().getActiveCell();
  var sheet = ss.getSheetByName("Тех");
  var rowNum = activeCell.getRow();
  var accID = ss.getSheetByName("Тех").getRange(rowNum , 7).getValue();
  var rkID = ss.getSheetByName("Тех").getRange(rowNum , 8).getValue();
  var options = "JLZlG30r5d7_NGLKyH-nTzwiKJFNNRWKKV"; 
  var rowExa = sheet.getRange(rowNum , 10);
  var rowExb = sheet.getRange(rowNum, 11);
  var rowExc = sheet.getRange(rowNum, 12);
  var rowExd = sheet.getRange(rowNum, 13);
  var rowExe = sheet.getRange(rowNum, 14);
  var curDate = new Date();
  var status = ss.getSheetByName("Тех").getRange(rowNum, 10).getValue();
  var response = UrlFetchApp.fetch("https://fbtool.pro/api/get-adaccounts?key=" + options + "&account=" + accID + "&ad_account=" + rkID);
  var datas = JSON.parse(response);
  var status = datas.data[0].account_status;
  var currency = datas.data[0].currency;
  var trshhold = datas.data[0].adspaymentcycle.data[0].threshold_amount/100;
  var curntspent = datas.data[0].current_unbilled_spend.offsetted_amount/100;
  var totalspent = datas.data[0].amount_spent/100;    
  rowExa.setValue(status);
  rowExb.setValue(currency);
  rowExc.setValue(trshhold);
  rowExd.setValue(curntspent);
  rowExe.setValue(totalspent);
    if(ss.getSheetByName("Тех").getRange(rowNum, 15).getValue() == "" && status == "1") {
       ss.getSheetByName("Тех").getRange(rowNum, 15).setValue(curDate)
    } else { 
    ss.getSheetByName("Тех").getRange(rowNum, 16).setValue(curDate)
    }


I would be very grateful for any help in resolving this issue)

Answer the question

In order to leave comments, you need to log in

1 answer(s)
B
BasiC2k, 2020-11-23
@BasiC2k

1. Determine the last filled row in the column
2. Read the column into an array
3. Iterate through all the elements of the array. You start processing depending on the element number and its value.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question