dimanche 27 octobre 2019

Is there a way to check over a column range for a certain data?

I have 2 workbooks, WB A ==> has multiple sheets of the same form WB B ==> records certain cells from WB A sheets into a single row per sheet (log) the first column in WB B contains hyperlinks of all sheets in WB A accordingly.. I need to check over the hyperlinks column in WB B as follows: if hyperlink exist in column A, check for the next sheet in WB A ==> index++ ,,,,

else record the new data in a row after the last row,,,,

my code used to have clear() function to start submission from scratch to WB B, but it's not efficient, execution time wise. I am trying to manipulate my current code to check manually on existing records and i++/submit new row based on this logic.

you can ignore all missing lines of code because i have it working, i just need the logic of the idea of doing this, since I am not advanced in Apps Script. Thanks in advance for your help.

for(var index = 2; index < WBA.length; index++) 
  {
    var Sheet   = "https://docs.google.com/spreadsheets/d/WBA ID/edit#gid=";
    var SID     = WBA[index].getSheetId();
    var SheetID = Sheet + SID;
    var Data    = WBB.getDataRange().getValues();

    for(var i = 0; i < Data.length; i++)
    {
      if(Data[i][1] == SheetID)
        i++

      else
      {

    var lastRow = WBB.getLastRow() + 1;

    var Sheets_ID   = new Array()

    Sheets_ID.push( [WBA[index].getSheetId()] )
           WBB.getRange(lastRow,1).setFormula('=hyperlink("https://docs.google.com/spreadsheets/d/WBA ID/edit#gid=' + Sheets_ID +'")');

    var PN_Source    = WBA[index].getRange(6,3,1,1).getValues();      //getRange(row number, column number, number of rows, number of columns)
    var SC_Source      = WBA[index].getRange(8,3,1,1).getValues();

Aucun commentaire:

Enregistrer un commentaire