Unable to call DocumentApp.openById. with onEdit() in GooglApps Script and Sheets

Question

I'm still very new to Javascript and Apps script in particular so sorry if this is dumb. I'm currently trying to create a mail merge tool. The Idea is that each field in a spreadsheet is manually filled with data, and once complete, the user ticks a checkbox in column U (changes value to TRUE). Once ticked, the values in the row containing said checkbox are stored as variables, and used to replace placeholders in a duplicated Google doc template. the doc ID for the template is taken from the spreadsheet once the letter type has been selected by the user (I also tried hardcoding the ID with the same error)

I wrote some code which performed the outcome without the checkbox with no issue (just running the function)

I also wrote some different code which stores all the values in the variables when ticked.

However now when I combine the two, I get this error in the Execution log: Exception: You do not have permission to call DocumentApp.openById. Required permissions: https://www.googleapis.com/auth/documents at onEdit(mmt:12:23)

My guess is that the DocumentApp.openById method only works server-side, and I am now calling it client-side, but if that's the case, then I don't know if it is possible to authorise it to work client side or not

here is a stripped down version of my code

//@NotOnlyCurrentDoc

function onEdit(e) {
let currentDate = new Date();
var formattedDate = Utilities.formatDate(currentDate, "UTC", "dd/MM/yyyy");

var sheet = e.source.getActiveSheet();
if (sheet.getName() !== "mailMergeData") return;

var row = e.range.getRow();
var col = e.range.getColumn();

 if (col === 21 && row > 3) {
    if (sheet.getRange(row, 22).getValue() == "") {
      var values = sheet.getRange(row, 1, 1, 19).getValues()[0];
        var agentName = values[1];
        var date = values[2];
        var accountNo = values[3];
        var letter = values[17];
        var letterId = values[18];
        var numberofpages = values[19];

        var doc = DocumentApp.openById("letterId");

        var file = DriveApp.getFileById(doc.getId());
        var newDoc = file.makeCopy(accountNo + letter + formattedDate );
        var newDocId =  newDoc.getId()
        doc.saveAndClose();
        newDocFile = DocumentApp.openById(newDocId)
        var body = newDocFile.getBody();

        body.replaceText('{{Agent_Name}}',agentName);
        body.replaceText('{{Date}}',date);
        body.replaceText('{{Account_No}}',accountNo);
        body.replaceText('{{Letter}}',letter);


      }
    }
  }

any help or advice would be hugely appreciated!

thanks

My other guess is it could have something to do with the Docs API I have enabled the Docs API in Services, I even crammed every potential OAuth Scope that looked like that could be something to do with it in case that was it.

{
  "timeZone": "Europe/London",
  "dependencies": {
    "enabledAdvancedServices": [
      {
        "userSymbol": "Docs",
        "version": "v1",
        "serviceId": "docs"
      },
      {
        "userSymbol": "Drive",
        "version": "v2",
        "serviceId": "drive"
      },
      {
        "userSymbol": "Sheets",
        "version": "v4",
        "serviceId": "sheets"
      }
    ]
  },
 "oauthScopes": [
      "https://www.googleapis.com/auth/spreadsheets.readonly",
      "https://www.googleapis.com/auth/userinfo.email",
      "https://www.googleapis.com/auth/spreadsheets",
      "https://www.googleapis.com/auth/documents.readonly",
      "https://www.googleapis.com/auth/documents"
  ],
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8"
}

If you are using your script as a simple trigger of OnEdit, in your situation, please use the installable OnEdit trigger. In that case, please rename the function name from `onEdit` to `installedOnEdit` and install the OnEdit trigger to the renamed function, and test it again. [Ref](https://stackoverflow.com/q/71215657) — Tanaike, Feb 03 '23 at 00:04

score 0 · Accepted Answer · answered Feb 03 '23 at 02:09

Try using and installable onEdit trigger:

function onMyEdit(e) {
  const dt = new Date();
  const dts = Utilities.formatDate(dt, "UTC", "dd/MM/yyyy");
  const sh = e.range.getSheet();
  if (sh.getName() == "mailMergeData" && e.range.columnStart === 21 && e.range.rowStart > 3) {
      if (e.range.offset(0,1).getValues() == "") {
        let values = sh.getRange(e.range.rowStart, 1, 1, 19).getValues().flat();
        let agentName = values[1];
        let date = values[2];
        let accountNo = values[3];
        let letter = values[17];
        let letterId = values[18];
        let doc = DocumentApp.openById(letterId);
        let file = DriveApp.getFileById(doc.getId());
        let newDoc = file.makeCopy(accountNo + letter + dts);
        let newDocId = newDoc.getId()
        doc.saveAndClose();
        newDocFile = DocumentApp.openById(newDocId)
        let body = newDocFile.getBody();
        body.replaceText('{{Agent_Name}}', agentName);
        body.replaceText('{{Date}}', date);
        body.replaceText('{{Account_No}}', accountNo);
        body.replaceText('{{Letter}}', letter);
      }
  }
}

I can't believe it was this simple after trying to work this out for hours! Thanks! I've added time-based triggers before, so why just assigning an on-edit trigger never occurred to me, I do not know. If anyone else ends up in my predicament - This (after the above advice) made things far more clearerer https://developers.google.com/apps-script/guides/triggers/installable. The Rabbit hole I went down from the error would have never landed me there! — Phil Tomlinson, Feb 03 '23 at 13:34

Unable to call DocumentApp.openById. with onEdit() in GooglApps Script and Sheets

1 Answers1