CODEWITHSUNDEEP
loopsfor-loopgoogle-apps-scriptgoogle-sheets
Vidomina Sangre
Vidomina Sangre

Reputation: 55

Pulling scattered data from changing spreadsheet

I'm trying to write a script which has multiple parts. The current part (function copyOver) supposed to open a spreadsheet (by ID), extract specific data from it and insert it into a Master spreadsheet.

The part I'm stuck with is that the spreadsheet changes daily. Some days there are cells containing data of "Sea Water", other days there aren't any. Ideally, I was trying to write a script which loops through the sheet looking for the specific tab names in cells (for example: Sea water, chlorine content...) and extract the data from the row below, up until the second tab and so on. I would only need very specific data, like data from "C6", "E6", "F10", but these cells are always changing so I have to look for them by using the tab names in cells above them. There would be multiple arrays for each tab and the data coming with it.

Would that be possible to extract data this way and put them into an array containing the tab value as the header or title and the data connected to that specific tab.

var sourceID = "source sheet ID";
  var main = SpreadsheetApp.openById("master sheet ID"); // MASTER
  var source = SpreadsheetApp.openById(sourceID); //the workbook you're copying from
  var mainsheet = main.getSheetByName("Lab Data"); // the sheet you want to copy the stuff into
  var sourcesheet = source.getSheetByName("ANALYSIS REPORT"); // the sheet you're copying from  
  var dataRange = sourcesheet.getDataRange().getValues(); // gets sheet as data range


// finds SEA WATER tab in cell and gets the range below until the end of the document
// this is the range I need to find the rest of the tabs and the data under them.
  var cont = "SEA WATER";  
    var i = [];
    for (var y = 0; y < dataValues.length; y++){
      if(dataValues[y] == cont){
      i.push(y);
      } // end if
    } // end for 
    Logger.log(i);
    var Row = Number(i)+Number(dataRange.getRow()); // gets the row number of SEA WATER tab
    Logger.log("row number:   " + Row); 

    var swLast = sourcesheet.getLastRow();
    var swRange = sourcesheet.getRange(Row,2,swLast,11); 
    var swValues = swRange.getValues(); // range of needed information
    Logger.log("sw:   " + swValues);

    var con2 = "SW outlet SW from Coarse filtration"; // looking for the secondary tab, within the 
    range 
    I got from the first loop
    var res2 = [];
      for(var i2 = 0; i2 < swValues.length; i2++) {
      if(swValues[i2][4] === con2) res2.push(data[i2])
    var row2 = Number(i2)+Number(swRange.getRow());
    Logger.log("row2   " + row2); 
      } // for end

    var look1 = "SW outlet SW from Coarse filtration ";
    for(var i1 = 0; i1<dataRange.length;i1++){
      if(dataRange[i1][1] == look1){ 
        return i1+1;
      }
    } 
     Logger.log((i1+1));    
} // end of function

EDIT: Here's a link to a sheet. Required data starts from row 237, until row 268 - but this can change every day. (Deleted the information for privacy reasons.) Basically, I need all the cells with "x" in them, preferably together with the title cells above them, so I'll know what data it is.

Upvotes: 0

Views: 60

Answers (1)

ziganotschka
ziganotschka

Reputation: 26796

The following code loops through your headers in column B and finds the row in which your header of interest is located

Subsequently, it copies the data starting from this row to the last data row into the sheet "Lab Data":

function myFunction() {
  var sourceID = "source sheet ID";
  var main = SpreadsheetApp.openById("sourceID"); // MASTER
  var source = SpreadsheetApp.openById(sourceID); //the workbook you're copying from
  var mainsheet = main.getSheetByName("Lab Data"); // the sheet you want to copy the stuff into
  var sourcesheet = source.getSheetByName("ANALYSIS REPORT"); // the sheet you're copying from  
  var dataRange = sourcesheet.getDataRange()
  var dataValues=dataRange.getValues(); // gets sheet as data range
  for (var a = 0; a < dataValues.length; a++){
    if(dataValues[a][1]=="SEA WATER"){
      var Row=a+1;
      break;
    }
  }
  if(Row){
    var swLast = sourcesheet.getLastRow();
    var swRange = sourcesheet.getRange(Row,2,swLast,11); 
    swRange.copyTo(mainsheet.getRange(mainsheet.getLastRow()+1, 2)); 
  }
} // end of function

I hope this helps to solve your issue.

Upvotes: 1

Related Questions