Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>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.</p> <ol> <li>Using the <code>onFormSubmit()</code> trigger.</li> <li>Get the Ids column from the destination sheet.</li> <li>Flatten the resulting 2D array into a 1D array for easy searching (indexOf) <ul> <li>I've used the JS iterative function <code>reduce()</code> and a helper function called <code>flatten_(a,b)</code></li> <li>You can find more details at <a href="https://developer.mozilla.org/en-US/docs/JavaScript/Reference/Global_Objects/Array#Iteration_methods" rel="nofollow">MDN</a>.</li> </ul></li> <li>Organize the information coming in from the event.</li> <li>Update the appropriate row in the destination sheet.</li> </ol> <p>A few notes about the demo sheets:</p> <ul> <li>They are editable; so, please try it out, but be nice.</li> <li>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.</li> <li>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.</li> <li>You can find the spreadsheets here <a href="https://docs.google.com/spreadsheet/ccc?key=0Amf6KahEwgTbdG5vMHFMVkQyaTFRZnNpdy1vQXBxbnc#gid=1" rel="nofollow">Report Sheet</a> | <a href="https://docs.google.com/spreadsheet/ccc?key=0Amf6KahEwgTbdEplcDNsMXhkc1R5VXpoeGNpR1ozTkE#gid=1" rel="nofollow">Approval Sheet</a></li> </ul> <p>Here is the uncommented code:</p> <pre><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 &lt; 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); } </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POMatch Value in Two Spreadsheets, If Same Copy Cells from 1 and Paste to 2nd
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USfooby
    1. USfooby
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COI don't think this is going to work for my project because I have two separate spreadsheets (not two sheets in the same spreadsheet) and the info is entered via two forms. One form enters the PO information into the PO sheet and sends an email to the manager. Once the manager approves the PO request, the approvals form enters PO info into the Approvals sheet and I want it to update the PO sheet based on the PO ID number, but currently is its updating based on the row number. Does this make sense?
      singulars
      1. PO
      1. USuser1916075
    2. CO@user1916075, yes that does make sense, and what I have included above should work for you... Add this script to the destination spreadsheet and have `updateReportSheet()` as an `onEdit()` trigger. Then, instead of getting the `approvalSheet` and `reportSheet` from `SpreadsheetApp.getActive()` use `SpreadsheetApp.openById()`. The script should require no modification beyond that... I'll try to cook up something similar. The line `var rowToUpdate = this.indexOf(id) + 1;` is where the search for the appropriate row is occuring. If you have questions about how the code works, please let me know.
      singulars
      1. PO
      1. USfooby
    3. CO-----EDIT: After thinking on this for a few more moments, there are some other changes that will be required to suit your needs. Instead of doing this on edit, you can run the function on form submission and pass along just the information you need. Sorry for the confusion.
      singulars
      1. PO
      1. USfooby
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload