Search code examples
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-apps

How to fix {"result":"error","error":{"name":"Exception"}} in google apps scripts

.gs

var sheetName = 'Sheet1'
var scriptProp = PropertiesService.getScriptProperties()

function doPost (e){
  Logger.log(JSON.stringify(e))
  if (!e || !e.parameter){
    return;
  }

  var lock = LockService.getScriptLock();
  lock.tryLock(10 * 1000);

  try {
    var ss = SpreadsheetApp.getActive();
    var sheet1 = ss.getSheetByName(sheetName);
    var headers = sheet1.getRange(1, 1, 1, sheet1.getLastColumn()).getValues()[0];
    var nextRow = sheet1.getLastRow() + 1;
    var newRow = headers.map(function(header) {
      return header === 'Timestamp' ? new Date() : e.parameter[header]
    });
    sheet1.getRange(nextRow, 1, 1, newRow.length).setValues([newRow]);

    var TEMPLATE_ID = '....ID....'
    var PDF_FILE_NAME = newRow[3];
    var RESULTS_FOLDER_ID = '....ID....'
    var templateFile = DriveApp.getFileById(TEMPLATE_ID);
    var copyFile = templateFile.makeCopy();
    var copyId = copyFile.getId();
    var copyDoc = DocumentApp.openById(copyId);
    var copyBody = copyDoc.getActiveSection();
    var headerValue;
    var activeCell;
    var activeRow = sheet1.getRange(nextRow, 1, 1, newRow.length).getValues();

    for (var columnIndex = 0; columnIndex < headers.length; columnIndex++){
    headerValue = headers[columnIndex]
    activeCell = activeRow[0][columnIndex]
    copyBody.replaceText('<<' + headerValue + '>>', activeCell);
    copyDoc.saveAndClose();
    var newFile = DriveApp.createFile(copyFile.getAs('application/pdf'));
    copyFile.setTrashed(true);
    newFile.setName(PDF_FILE_NAME);
    if (RESULTS_FOLDER_ID !== ''){
        DriveApp.getFolderById(RESULTS_FOLDER_ID).addFile(newFile)
        DriveApp.removeFile(newFile)
    }
    }

    return ..
  }

  catch (e){
    return ContentService ..
  }

  finally{
    lock.releaseLock()
  }
}

The purpose of the code is to:

  • Post data to google sheet

  • Create a new pdf file every time a new row is submitted using the new row data

I receive the data in the sheet successfully and also the new pdf is created in the drive but with the following errors:

  • I am supposed to have 4 updated fields but only the first one is updated in the pdf

In the template doc I have: Name1, Name2, Name3, Name4 to be updated as in the sheet header

So when a new row is submitted to the sheet for example with Tom, Mike, John, Andy - I get that in the pdf Tom, Name2, Name3, Name4 (only the first value is updated)

  • Redirected to {"result":"error","error":{"name":"Exception"}} after submit

.html

<!DOCTYPE html>
<html lang="en">
<body>
<head>
<base target="_top">
</head>
<form action="....URL...." target="_blank" method="POST">
<div>
Name1
<input type="text" required name="Name1">
</div>

<div>
Name2
<input type="text" required name="Name2">
</div>

<div>
Name3
<input type="text" required name="Name3">
</div>

<div>
Name4
<input type="text" required name="Name4">
</div>

<button type="submit">Post</button>

</form>
</body>
</html>

Any help is appreciated. Thanks in advance

Solution

  • How about this modification? Please think of this as just one of several possible answers.

    Modification points:

    • In your script, createFile and setTrashed are run in the for loop of for (var columnIndex = 0; columnIndex < headers.length; columnIndex++){}. In this case, at the 1st loop, a PDF file is created, and copyFile is moved to the trash box. I think that the reason of your issue might be this.

    In order to avoid above situation, how about the following modification?

    Modified script:

    When your script is modified, please modify as follows.

    From: 
    for (var columnIndex = 0; columnIndex < headers.length; columnIndex++){
headerValue = headers[columnIndex]
activeCell = activeRow[0][columnIndex]
copyBody.replaceText('<<' + headerValue + '>>', activeCell);
copyDoc.saveAndClose();
var newFile = DriveApp.createFile(copyFile.getAs('application/pdf'));
copyFile.setTrashed(true);
newFile.setName(PDF_FILE_NAME);
if (RESULTS_FOLDER_ID !== ''){
    DriveApp.getFolderById(RESULTS_FOLDER_ID).addFile(newFile)
    DriveApp.removeFile(newFile)
}
}
    To: 
    for (var columnIndex = 0; columnIndex < headers.length; columnIndex++){
  headerValue = headers[columnIndex]
  activeCell = activeRow[0][columnIndex]
  copyBody.replaceText('<<' + headerValue + '>>', activeCell);
}
copyDoc.saveAndClose();
var newFile = DriveApp.createFile(copyFile.getAs('application/pdf'));
copyFile.setTrashed(true);
newFile.setName(PDF_FILE_NAME);
if (RESULTS_FOLDER_ID !== ''){
  DriveApp.getFolderById(RESULTS_FOLDER_ID).addFile(newFile);
  DriveApp.removeFile(newFile);
}

    Note:

    • When you modified the script of Web Apps, please redeploy the Web Apps as new version. By this, the latest script is reflected to the Web Apps. Pleases be careful this.

    If I misunderstood your question and this was not the result you want, I apologize. At that time, in order to correctly understand about your situation, can you provide a sample Spreadsheet including your script? By this, I would like to confirm it. Of course, please remove your personal information.