StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<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 < 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>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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