1

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("");
    }
  }
}
Community
  • 1
  • 1
Starfall
  • 19
  • 4
  • This is covered in my answer to this s.o. question (posible dup) http://stackoverflow.com/questions/19382538/not-allow-to-access-from-one-to-other-google-spreadsheet/19383525#19383525 – Zig Mandel Oct 23 '13 at 23:38
  • HaHAAA! Thank you so much! I'd prefer to leave this up in case other people are searching for similar issues. While I had been searching using openById and the various errors I could produce, the one you linked never came up. This might help direct others having the same issue. – Starfall Oct 24 '13 at 02:14

0 Answers0