Pulling scattered data from changing spreadsheet

Question

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.

It is possible, but it would be easier to understand the exact issue if you could provide a sample sheet (you can change your data through dummy data to maintain privacy). Does the position of the headers also vary? Do you always want to copy only one row per header or can there be several? — ziganotschka
Sure, sorry about that! I edited my question, I hope it helps to see my problem better. Removed the data for privacy reasons, hope it's still understandable what I'm trying to do. The position of headers vary too, they're always in different rows, but they always called the same - except the "taken at" part where the time changes also — Vidomina Sangre
Can you show a sample sheet of a day where there are no cells of sea water? Is there no sea water data at all, or is only some of the data populated? Are all the headers and subheaders (like e.g. coarse filtration sw) still in the spreadsheet? Is "SEA WATER" the last header of the sheet? — ziganotschka
Sorry, I should have explained it better. "SEA WATER" is always there. The blue headers starting in column B are the main ones, always there, but can change rows depending on how much data there is before them. Sub headers (like Coarse Filtration) can be included or not on the sheet (that is changing). For example if the sub header is not on the sheet, the smaller underlined header directly under it won't be there, neither will the data All of the cells are populated, between column J and AG, however, I only need those specific cells in this case. — Vidomina Sangre

ziganotschka ziganotschka · Accepted Answer · 2019-12-17T12:39:26

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.

Pulling scattered data from changing spreadsheet

1 Answers

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