CODEWITHSUNDEEP
if-statementgoogle-apps-scriptgoogle-sheetsmatch
Samy
Samy

Reputation: 63

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

I have 2 workbooks, Spreadsheet A has multiple sheets of the same form.

Spreadsheet B records contain cells from Spreadsheet A sheets into a single row per sheet (log).

The first column in Spreadsheet B contains hyperlinks of all sheets in Spreadsheet A. I need to check the hyperlinks column in Spread B as follows: if hyperlink exist in column A, check for the next sheet in Spreadsheet 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 Spreadsheet 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.

Spreadsheet A - Sheet1 SpreadsheetA-sheet1

Spreadsheet A - Sheet2 SpreadsheetA-sheet2

Spreadsheet B

Workbook B Log

I want the if statement to run on the Sheet link column in Workbook B.... in case of adding Sheet #3 to Workbook A (picture 1&2), upon submission, I want to check on Sheet links in Workbook B log (because it's unique) if it exists, i++.... if doesn't exist, add 3rd record in log.

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

Upvotes: 0

Views: 116

Answers (1)

Cooper
Cooper

Reputation: 64042

Try this:

function checkForLink() {
  var ssB=SpreadsheetApp.getActive();
  var sh1=ssB.getActiveSheet();
  var rg1=sh1.getRange(1,1,1,4);
  var vA1=rg1.getValues()[0];
  var hObj={};
  vA1.forEach(function(el,i){if(el){hObj[el]=i;}});
  var linkA=sh1.getRange(2,hObj['Sheet Link']+1,sh1.getLastRow(),1).getValues().map(function(r){return r[0];});
  var ssA=SpreadsheetApp.openById('SSA ID');
  var shts=ssA.getSheets();
  shts.forEach(function(sh,i){
    var vA=sh.getRange(1,1,2,sh.getLastColumn()).getValues();
    var sObj={};
    vA[0].forEach(function(e,i){sObj[e]=i;});
    var shlink=Utilities.formatString('https://docs.google.com/spreadsheets/d/%s//edit#gid=%s',ssA.getId(),sh.getSheetId());
    if(linkA.indexOf(shlink)==-1) {
      sh1.appendRow([vA[1][sObj.date],vA[1][sObj.age],vA[1][sObj.name],shlink]);
    } 
  });
}

Upvotes: 1

Related Questions