Returning row values based on specific variables across multiple sheets

Question

I'm not entirely sure how to properly explain this, but here we go...

I'm trying to create a single budgeting document that allows me to manage purchasing and reconciliation for multiple projects. I would like to create separate sheets per project and have purchased items populate on a master sheet.

Using conditional formatting, I've set one of the columns to display an item's status (waiting for approval, approved, ordered, received). I would like the contents of an entire row to populate in a new sheet table once the status is set to "Received." The sheet should update descendingly.

Any help is greatly appreciated.

Answer 1

After reviewing your question a little closer, I determined that you would need to write a google script to accomplish this.

All you need to do is: open up the google script editor, make a new project, paste this in the bottom of the script, rename the "Reconciliation" to be whatever the name of your spreadsheet is, and then save it. The onEdit() is a trigger built into google scripts that does the work of updating after an edit, and the checkReceived() is a hand rolled example that does the work you seek. I tailored this "example" to your image provided. You will see that you can add as many projects as you want and this will cover them just the same. You can change the particulars to match whatever else you need (ie you should also note that I called the master sheet "Master"), but this should work for you. Please let me know how it goes.

function checkReceived() {


  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  //The following to ensures you don't run this on another spreadsheet
  if(spreadsheet.getName()=="Reconciliation") //<--This is the name of the spreadsheet I used, change it for yours.
  {  

    var maxIntervalSS = spreadsheet.getNumSheets();
    var theMasterSheet = spreadsheet.getSheetByName("Master");
    var masterSheetID = theMasterSheet.getIndex();
    var sheets = spreadsheet.getSheets();
    var thisRow = theMasterSheet.getRange(1,1); //Arbitrary for initialization

    //Need to Clear existing master data.
    theMasterSheet.getDataRange().clear();

    //Will use a flag to repopulate header.
    var firsttime= true;

    //Iterates through each spreadsheet
    for(var checkingSheetIterator = 0; checkingSheetIterator<maxIntervalSS; checkingSheetIterator++)
    {

      var currentSheet = sheets[checkingSheetIterator];

      //Ignores the iteration if ID equals the master sheet
      if(currentSheet.getIndex()!=masterSheetID)
      {




        //Getting currentSheet's data
        var currentRange = currentSheet.getDataRange();

        //Iterates through the currentSheet's data
        for(var rows = 1; rows<=currentSheet.getLastRow(); rows++)
        {
          //Repopulating header on first time.              
          //"8" for column H, containing the Status
          if(currentRange.getCell(rows,8).getValue()=="Received"|| firsttime)  //<-Note this is your keyword and specified location; "8".
          {  //Add to bottom of sheet
            theMasterSheet.appendRow([currentRange.getCell(rows,1).getValue(),currentRange.getCell(rows,2).getValue(),
                                      currentRange.getCell(rows,3).getValue(),currentRange.getCell(rows,4).getValue(),
                                      currentRange.getCell(rows,5).getValue(),currentRange.getCell(rows,6).getValue(),
                                      currentRange.getCell(rows,7).getValue(),currentRange.getCell(rows,8).getValue(),
                                      currentRange.getCell(rows,9).getValue(),currentRange.getCell(rows,10).getValue(),
                                      currentRange.getCell(rows,11).getValue()]);
            firsttime=false;
          }
        }
      }
    }
  }
};  

function onEdit(){

  checkReceived();

};

The following is an example of the spreadsheet setup. Notice the name of the spreadsheet is Reconciliation (top of the picture), and the tabs (ie sheets) at the bottom of the page are named "Master", "Project1", and "Project2". Please note, that I didn't bother with the conditional formatting as it shouldn't have any bearing on the outcome.