CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
user1916075
user1916075

Reputation: 91

Match Value in Two Spreadsheets, If Same Copy Cells from 1 and Paste to 2nd

I used the expense report tutorial to generate most of my code.

The main thing I changed is that instead of the expense report ID being the row number, I made it into a random number (ll-nnnn). I would like to use the report ID as the identifier between the expense report sheet and the expense approvals sheet, so I added a report ID column to the report sheet. I want the script to take the report ID from the approvals form, and search the report sheet ID column for the row with that ID in it, then enter in whether it was approved or denied and whatever comments the manager had in the corresponding row in column M and N. In VBA the the code I would use would be and If statement with a match condition. I am new to java script so I am not sure how to do this. ***I have edited my code using what fooby provided. When I run it, I get the error "The coordinates or dimensions of the range are invalid." on the line: " poSheet.getRange(rowToUpdate, 2, 1, 13).setValues(updateInfo);" Any suggestions would be greatly appreciated! 

    var PO_SPREADSHEET_ID= "0At0Io3p64FeddFVCV2lJdEpLY09MYWNkVS05NDhzWkE";
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[0];
    var poSs = SpreadsheetApp.openById(PO_SPREADSHEET_ID);
    var poSheet = poSs.getSheets()[0];

    function updatePOSheet() {
    // Get IDs from Approval Sheet
    var row = sheet.getRange(2, 1, sheet.getLastRow()-1,sheet.getLastColumn()).getValues();
    // Get IDs from Report Sheet, flatten it into a 1D array
    var poId = poSheet.getRange(2,1,poSheet.getLastRow()-1,1).getValues().reduce(flatten_);
   // For each row in the Approval Sheet update the coresponding report row
   // reportIds is passed a "this" in the forEach function, see below
      row.forEach(updateReportRow_,poId);

      }

   // Checks to see if the status of a row is either "Approved" or "Denied
  // then updated the correct row in the reports sheet with manager's comments
     function updateReportRow_(row) {
     var id = row[2];
     var status = row[3];
     var comments = row[4];

  // Get row in reports sheet from reportIds (it was passed as 'this')
     var rowToUpdate = this.indexOf(id) + 1; 

  // Put info into spreadsheet friendly array
     var updateInfo = [[status,comments]];

     if (status == "Approved" || status == "Denied") {
     poSheet.getRange(rowToUpdate, 2, 1, 13).setValues(updateInfo);
       }
     }

  // Returns a piece of an array concatenated with the element on to it's right
  // will make [[0],[1],[2]...]] into [0,1,2,...]
  function flatten_(a,b) {
  return a.concat(b);
  }

Upvotes: 0

Views: 668

Answers (1)

fooby
fooby

Reputation: 849

I used this kind of technique all the time, and I tried to just explain it here, but it wasn't going very well. So, I put together, as best I could, a demo sheet of how you can accomplish what you desire. Before I get to that, here is the general gist of the approach and the uncommented code.

  1. Using the onFormSubmit() trigger.
  2. Get the Ids column from the destination sheet.
  3. Flatten the resulting 2D array into a 1D array for easy searching (indexOf)
    • I've used the JS iterative function reduce() and a helper function called flatten_(a,b)
    • You can find more details at MDN.
  4. Organize the information coming in from the event.
  5. Update the appropriate row in the destination sheet.

A few notes about the demo sheets:

  • They are editable; so, please try it out, but be nice.
  • The Report Sheet (the destination sheet) has some hard coded values. The OP is filling this in with a form which I did not build.
  • The Approval Form is not smart, you must write in an existing ID. Ultimately, a custom GUI would be needed to make this really slick.
  • You can find the spreadsheets here Report Sheet | Approval Sheet

Here is the uncommented code:

function updateReportRow(approvalInfo) {
  var id = approvalInfo.namedValues.ID;
  var status = approvalInfo.namedValues.Status;
  var comments = approvalInfo.namedValues.Comments;
  var reportSheet = SpreadsheetApp.openById("SheetID").getSheetByName("Report Sheet");
  var reportIds = reportSheet.getRange(1,1,reportSheet.getLastRow(),1).getValues().reduce(flatten_);
  var rowToUpdate = reportIds.indexOf(id.toString())+1;
  if (rowToUpdate < 2) {
    // Something went wrong...email someone!
  } else {
    if (status == "Approved") {
      var updateInfo = [[status,comments,"STATE_APPROVED"]];
      reportSheet.getRange(rowToUpdate, 5, 1, updateInfo[0].length).setValues(updateInfo);
    } else if (status == "Denied") {
      var updateInfo = [[status,comments,"STATE_DENIED"]];
      reportSheet.getRange(rowToUpdate, 5, 1, updateInfo[0].length).setValues(updateInfo);
    }
  }
}

function flatten_(a,b) {
  return a.concat(b);
}

Upvotes: 1

Related Questions