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"
}

CodePudding user response：

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);
      }
  }
}