I have a Google Form that takes requests for new items for our library collection. The spreadsheet has a script to copy data between four sheets for the four people who will be dealing with them based on the type of item.
These people would like to be able to edit a column in their own respective sheets that has the status of each item (pending, not available, received, etc), and have that edited status automatically update on the original sheet that receives the form submissions.
This is the part that I am stuck on (I marked in the script as well). The logic that I have come up with is that each form submission has a timestamp, so on each edit, it should run a loop that matches the timestamp on the edited sheet with the one on the original submissions sheet and from there copies the edit to the main submissions sheet. I have no idea what I am doing and have just kind of typed up what I think makes sense, but when I run it, it doesn't even initiate the for loop.
Link to Copy of Form:(removed)
Link to Editable copy of Spreadsheet: (removed)
function onFormSubmit() {//Distributes Form Submissions to the appropriate sheets
//depending on format and audience whenever new submission is made
var ss = SpreadsheetApp.getActiveSpreadsheet();
var responseSheet = ss.getSheetByName("Item Request"); //points to form response sheet
var lastSub = responseSheet.getLastRow(); //retreives last row (assumed last submission)
//the following point to the cells within the last submission row
var tsCell = responseSheet.getRange(lastSub,1);
var lnCell = responseSheet.getRange(lastSub,2);
var fnCell = responseSheet.getRange(lastSub,3);
var lCardCell = responseSheet.getRange(lastSub,4);
var phoneCell = responseSheet.getRange(lastSub,5);
var audienceCell = responseSheet.getRange(lastSub,6);
var formatCell = responseSheet.getRange(lastSub,7);
var genreCell = responseSheet.getRange(lastSub,8);
var titleCell = responseSheet.getRange(lastSub,9);
var authorCell = responseSheet.getRange(lastSub,10);
var isbnCell = responseSheet.getRange(lastSub,11);
var pubCell = responseSheet.getRange(lastSub,12);
var staffCell = responseSheet.getRange(lastSub,13);
var notesCell = responseSheet.getRange(lastSub,14);
//the following retreive values from those cells
var tsVal = tsCell.getValue();
var lnVal = lnCell.getValue();
var fnVal = fnCell.getValue();
var lCardVal = lCardCell.getValue();
var phoneVal = phoneCell.getValue();
var audienceVal = audienceCell.getValue();
var formatVal = formatCell.getValue();
var genreVal = genreCell.getValue();
var titleVal = titleCell.getValue();
var authorVal = authorCell.getValue();
var isbnVal = isbnCell.getValue();
var pubVal = pubCell.getValue();
var staffVal = staffCell.getValue();
var notesVal = notesCell.getValue();
//the following if statement determines which sheet the response values will be copied to
if (audienceVal == "Spanish" || formatVal == "e-Book" || formatVal == "e-Audio") {
var selector = "Person 1";
targetSheet = ss.getSheetByName(selector);
}
else if (audienceVal == "Juvenile" ) {
var selector = "Person 2";
targetSheet = ss.getSheetByName(selector);
}
else if (audienceVal == "Young Adult" ) {
var selector = "Person 3";
targetSheet = ss.getSheetByName(selector);
}
else {
var selector = "Person 4";
targetSheet = ss.getSheetByName(selector);
}//end if
targetSheet.appendRow([tsVal,lnVal,fnVal,lCardVal,phoneVal,audienceVal,formatVal,genreVal,titleVal,authorVal,isbnVal,pubVal,staffVal,notesVal]);
var selectorCell = responseSheet.getRange(lastSub,15);
selectorCell.setValue(selector);
}//end onFormSubmit
function onEdit() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var actSheet = ss.getActiveSheet();
var responseSheet = ss.getSheetByName("Item Request");
var actCell = actSheet.getActiveCell();
var actVal = actCell.getValue();
var actLoc = actCell.getA1Notation();
var last = actSheet.getLastRow();
var respLast = responseSheet.getLastRow();
var dataA = actSheet.getRange(1,1,respLast,1); //compiles an array of data found in column A through last row in response sheet
var dataO = actSheet.getRange(1,1,last,15); //compiles an array of data found in column O through last row in active sheet
var dataP = actSheet.getRange(1,1,last,16); //compiles an array of data found in column P through last row in active sheet
var tstamp1 = actSheet.getRange(actCell.getRow(),1);
var tsVal1 = tstamp1.getValue();
//=============****THIS IS WHERE I'M STUCK****================
for (i=0;i<dataO.length;++i){
if (dataO[i][0] == actCell) { //checks the array to see if the edit was made to the "O" column
//----------(checking for timestamp match and copying entry)----------------------
for (i=0;i<dataA.length;++i){
if (dataA[i][0] == tsVal1){
var tstamp2 = responseSheet.getRange(dataA[i][0]);
var toEdit = responseSheet.getRange(tstamp2.getRow(),16);
toEdit.setValue(actVal);
}
}
}
else { //if edit was not made in col "O,"checks if edit was in "P" column
for (i=0;i<dataP.length;++i){
if (dataP[i][0] == actCell) {// checks the array to see if the edit was made in the "P" column
//------------(checking for timestamp match and copying entry)----------------------
for (i=0;i<dataA.length;++i){
if (dataA[i][0] == tsVal1){
var tstamp2 = responseSheet.getRange(dataA[i][0]);
var toEdit = responseSheet.getRange(tstamp2.getRow(),17);
toEdit.setValue(actVal);
}
}
}
else {return;}
}
}
}
}
