Intended Operation: I have two spreadsheets (note I did not say sheets within a spreadsheet, but two entirely separate spreadsheets).
One spreadsheet receives submissions from a Google Forms. A team monitors the submissions and sets each one to various categories: Claimed, Unclaimed, Resolved, Pending. When things are marked as Pending, I need the row that was marked to be copied to the second spreadsheet, where a separate team handles it.
Important Note: IMPORTRANGE will not suffice. The second team needs to manipulate the data copied, and IMPORTRANGE and similar functions don't allow that. As a result, I have been trying to use appendRow()
instead.
Problem: For reasons unknown, everything works up until the code gets to the point of openById()
, at which point the execution fails with the following error:
[13-10-23 15:00:59:117 PDT] SpreadsheetApp.openById([ss-key]) [0 seconds] [13-10-23 15:00:59:133 PDT] Execution failed: Action not allowed (line 26, file "PK-PR Manager") [0.267 seconds total runtime]
What I've Tried: I've tried saving my script project as a library and adding it to the other spreadsheet, I've tried revoking the script access to my account and re-enabling it. I've tried looking for ways to link the script to both spreadsheets (without success obviously). I've searched Google extensively both in documentation and anything I could find involving the functions mentioned. I have checked, double-checked and triple-checked all of my variables, syntax, etc. I've also checked and changed the sharing permissions on the target spreadsheet, just in case.
The Actual Code:
function onEdit(e) {
if (e.range.getColumn() == 2) {
var myCell = e.range;
status = myCell.getValue();
myCell.setFontWeight("bold");
if (status == "Claimed") {
myCell.setBackgroundColor("#F5B36C");
myCell = myCell.offset(0,1);
myCell.setValue(Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd' 'HH:mm:ss"));
} else if (status == "Resolved") {
myCell.setBackgroundColor("#95C47E");
} else if (status == "Pending") {
myCell.setBackgroundColor("#B4A7D6");
myCell = myCell.offset(0,1);
myCell.setValue(Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd' 'HH:mm:ss"));
var myRow = myCell.getRow();
var lineRange = master.getRange("C" + myRow + ":L" + myRow);
var lineValues = lineRange.getValues();
var pkss = SpreadsheetApp.openById(pkssKey);
//Logger.log(pkss.getName());
var pksheet = pkss.getSheetByName("Sheet1");
pksheet.appendRow(lineValues[0]);
} else {
myCell.setBackgroundColor("#E06767");
myCell.setValue("Unclaimed");
myCell = myCell.offset(0,1);
myCell.setValue("");
myCell = myCell.offset(0,1);
myCell.setValue("");
}
}
}