I read this and this but did not find an answer to my issue. I've the below code, that is receiving JSON data from the form, and insert data at the SpreedSheet, :
function doPost(e) {
// Prevent concurrent access overwritting data
// we want a public lock, one that locks for all invocations
var lock = LockService.getPublicLock();
lock.waitLock(30000); // wait 30 seconds before conceding defeat.
// As we are passing JSON in the body, we need to unpairse it
var jsonString = e.postData.getDataAsString();
e.parameter = JSON.parse(jsonString);
try {
// next set where we write the data - you could write to multiple/alternate destinations
// var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var doc = SpreadsheetApp.openById(FILE_Id);
var sheet = doc.getSheetByName(SHEET_NAME);
// we'll assume header is in row 1 but you can override with header_row in GET/POST data
var headRow = e.parameter.header_row || 1;
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var nextRow = sheet.getLastRow()+1; // get next row
var row = [];
// loop through the header columns
for (i in headers){
if (headers[i] == "Timestamp"){ // special case if you include a 'Timestamp' column
row.push(new Date());
} else { // else use header name to get data
row.push(e.parameter[headers[i]]);
}
}
// more efficient to set values as [][] array than individually
sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
// return json success results
return ContentService
.createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
.setMimeType(ContentService.MimeType.JSON);
} catch(e){
// if error return this
return ContentService
.createTextOutput(JSON.stringify({"result":"error", "error": e}))
.setMimeType(ContentService.MimeType.JSON);
} finally { //release lock
lock.releaseLock();
}
}
Execution is done perfectly in terms of adding data to the sheet, but I've issue in the return, as I got the below string:
<!DOCTYPE html><html><head><link rel="shortcut icon" href="//ssl.gstatic.com/docs/script/images/favicon.ico"><title>خطأ</title><style type="text/css">body {background-color: #fff; margin: 0; padding: 0;}.errorMessage {font-family: Arial,sans-serif; font-size: 12pt; font-weight: bold; line-height: 150%; padding-top: 25px;}</style></head><body style="margin:20px"><div><img alt="Google Apps Script" src="//ssl.gstatic.com/docs/script/images/logo.png"></div><div style="text-align:center;font-family:monospace;margin:50px auto 0;max-width:600px">اكتمل النص البرمجي إلا أنه لم يعرض شيئًا.</div></body></html>
The translation of the Arabic text is: Script completed but didn't display anything
UPDATE
I modified my code to use single return statement as below, but still getting the same message:
function handleResponse(e) {
var result, message;
// Prevent concurrent access overwritting data
// we want a public lock, one that locks for all invocations
var lock = LockService.getPublicLock();
lock.waitLock(1000); // 3000 wait 30 seconds before conceding defeat.
// As we are passing JSON in the body, we need to unpairse it
var jsonString = e.postData.getDataAsString();
e.parameter = JSON.parse(jsonString);
try {
// next set where we write the data - you could write to multiple/alternate destinations
// var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var doc = SpreadsheetApp.openById(FILE_Id);
var sheet = doc.getSheetByName(DATA_SHEET);
//var report = doc.getSheetByName(REPORT_SHEET);
// we'll assume header is in row 1 but you can override with header_row in GET/POST data
// var headRow = e.parameter.header_row || 1;
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var lastRow = sheet.getLastRow()
var nextRow = lastRow + 1; // get next row
var row = [];
if(lastRow < 10){
RefID = "PRF.00" + lastRow
} else {
if(lastRow < 100){
RefID = "PRF.0" + lastRow
} else {
RefID = "PRF." + lastRow
}
}
// loop through the header columns
for (i in headers){
if (headers[i] == "Ref"){ // special case if you include a 'Timestamp' column
row.push(RefID);
} else { // else use header name to get data
if (headers[i] == "Timestamp"){ // special case if you include a 'Timestamp' column
row.push(new Date());
} else { // else use header name to get data
row.push(e.parameter[headers[i]]);
}
}
}
// more efficient to set values as [][] array than individually
sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
/*********************************************/
var link = printPDF(RefID)
console.log("PDF created")
/*********************************************/
// return json success results
result = "success";
message = link;
} catch(e){
// if error return this
result = "error";
message = e;
} finally { //release lock
lock.releaseLock();
}
return ContentService
.createTextOutput(JSON.stringify({"result":result, "message": message}))
.setMimeType(ContentService.MimeType.JSON);
}
I found the error to be as below:
the doPost(e) is calling handleResponse(e) the system expect the return to be from the doPost(e) itself.
I made a mistake in my question in real, first in the snippets in my question (for code simplicity) I mentioned the call is done from the doPost(e) which looks to be mislead the friends who read my question.
Now my working code is:
function doPost(e){
output = handleResponse(e)
return ContentService.createTextOutput(output)
.setMimeType(ContentService.MimeType.JSON);
}
function handleResponse(e) {
var result, message;
// Prevent concurrent access overwritting data
// we want a public lock, one that locks for all invocations
var lock = LockService.getPublicLock();
lock.waitLock(1000); // 3000 wait 30 seconds before conceding defeat.
// As we are passing JSON in the body, we need to unpairse it
var jsonString = e.postData.getDataAsString();
e.parameter = JSON.parse(jsonString);
try {
// next set where we write the data - you could write to multiple/alternate destinations
// var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var doc = SpreadsheetApp.openById(FILE_Id);
var sheet = doc.getSheetByName(DATA_SHEET);
//var report = doc.getSheetByName(REPORT_SHEET);
// we'll assume header is in row 1 but you can override with header_row in GET/POST data
// var headRow = e.parameter.header_row || 1;
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var lastRow = sheet.getLastRow()
var nextRow = lastRow + 1; // get next row
var row = [];
if(lastRow < 10){
RefID = "PRF.00" + lastRow
} else {
if(lastRow < 100){
RefID = "PRF.0" + lastRow
} else {
RefID = "PRF." + lastRow
}
}
// loop through the header columns
for (i in headers){
if (headers[i] == "Ref"){ // special case if you include a 'Timestamp' column
row.push(RefID);
} else { // else use header name to get data
if (headers[i] == "Timestamp"){ // special case if you include a 'Timestamp' column
row.push(new Date());
} else { // else use header name to get data
row.push(e.parameter[headers[i]]);
}
}
}
// more efficient to set values as [][] array than individually
sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
/*********************************************/
var link = printPDF(RefID)
console.log("PDF created")
/*********************************************/
// return json success results
result = "success";
message = link;
} catch(e){
// if error return this
result = "error";
message = e;
} finally { //release lock
lock.releaseLock();
var output = JSON.stringify({"result":result, "message": message});
}
return output;
}