Read Message from WebApp-CodePudding

I am working on a WebApp Add-on in Google Sheets. Below is the Addon and webapp script. I want to read validation/error message from webapp and display to user. Like I will send message from doPost(e) like "Check Values" and user should get this as message box.

function copyData() {
 var ss_id = SpreadsheetApp.getActive().getId();
  
  //This is the Web App URL.
  var url = "https://script.google.com/macros/s/<id>/exec";  

  var payload = {
  "ss_id" : ss_id, // Modified
}

  var options = {
    "method" : "POST",
    "payload" : payload,
    "followRedirects" : true,
    "muteHttpExceptions" : true,
  };

  var result = UrlFetchApp.fetch(url, options);  
  
}

function doPost(e) {

  var ss_id = e.parameter.ss_id; // Modified

var response = {
  "status" : "FAILED",
  "ss_id" : ss_id,
};

  //var ss_id = ss_id[0];
  
   //Use your spreadsheetID to get Output Sheet
  var Manager_SS=SpreadsheetApp.openById('<id>');
  var Manager_Sheet=Manager_SS.getSheetByName('Consolidated_Data');
    
  var FrontDesk_ss = SpreadsheetApp.openById(ss_id);
  var FrontDesk_sheet = FrontDesk_ss.getSheetByName('Data');
  
  //Get front desk data
  
  var sData = FrontDesk_sheet.getRange("A2:C10").getValues();

  //Copy data from Front Desk to Manager Sheet. 
  Manager_Sheet.getRange("A2:C10").clear();
  Manager_Sheet.getRange("A2:C10").setValues(sData);

  //Update done after copying data.
  FrontDesk_sheet.getRange('D1:D10').setValue('Done');
  
  var response = {
    "status" : "SUCCESS",
    "sData" : sData,
  };     

  return ContentService.createTextOutput(JSON.stringify(response));

}

CodePudding user response：

Reading Validation Error from Webapp

html:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <form>
      <input type="text" id="txt1" name="id" placeholder="Enter Numbers only"/>
      <input type="button" value="submit" onClick="processForm(this.parentNode);" />
    </form>
    <script>
      function processForm(obj) {
        console.log(obj.id.value);
        if(obj.id.value.match(/[A-Za-z]/)) {
          google.script.run.displayError("Invalid Characters Found in id field");
        } else {
          google.script.run.sendData(obj);
        }
      }
    </script>
  </body>
</html>

GS:

function doPost(e) {
  Logger.log(e.postData.contents);
  Logger.log(e.postData.type);
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet1");
  let data = JSON.parse(e.postData.contents);
  let row = [];
  Object.keys(data).forEach(k => row.push(data[k]));
  Logger.log(JSON.stringify(row))
  sh.appendRow(row);
}

function sendData(obj) {
  const url = ScriptApp.getService().getUrl();
  const params={"contentType":"application/json","payload":JSON.stringify(obj),"muteHttpExceptions":true,"method":"post","headers": {"Authorization": "Bearer "    ScriptApp.getOAuthToken()}};
  UrlFetchApp.fetch(url,params);
}

function displayError(msg) {
  SpreadsheetApp.getUi().alert(msg);
}

function launchMyDialog() {
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutputFromFile('ah1'),'My Dialog');
}

CodePudding user response：

For this example I am using a bounded script, but this should be the same for an Editor Add-on

In the spreadsheet we want to validate, we create a custom menu to call a function that makes a POST request to our Web App. Depending on the response, we display one content or another.

const UI = SpreadsheetApp.getUi()
const onOpen = () => {
  /* Adds the custom menu */
  UI.createMenu('Custom Function').addItem('Is Valid?', 'checkValidity').addToUi()
}
const checkValidity = () => {
  const res = UrlFetchApp.fetch
    (
      /* Change it for your URL */
      "https://script.google.com/macros/s/<ID>/exec",
      {
        "method": "post",
        "contentType": "application/json",
        /* In this example I only send the ID of the Spreadsheet */
        "payload": JSON.stringify(
          {
            "ss_id": SpreadsheetApp.getActiveSpreadsheet().getId()
          }
        )
      }
    )
  /* Depending on the response from the Web App */
  /* We show different messages */
  const { MSG } = JSON.parse(res.getContentText())
  UI.alert(MSG === "OK" ? "IS VALID" : "IS NOT VALID")
}

After we create a Web App that validates the ID. In this example I am only validating that the ID is contained in an array of valid IDs, but this should be replaced by whatever you need. As a response I only send a simple "OK" or "NOT OK", but this can be replaced with any kind of data.

const doPost = (e) => {
  /* We perform the checks we need */
  /* In this example only checking if the id is contained in an array */
  /* This should be changed to perform the desired checks */
  const validID = ["<VALID_ID_1>","<VALID_ID_2>"]
  const { ss_id } = JSON.parse(e.postData.contents)
  /* SpreadsheetApp.openById(ss_id).copy("my_new_copy") */
  const checker = validID.includes(ss_id)
  /* We send back the response */
  /* Depending on the checker value */
  return ContentService.createTextOutput(JSON.stringify(
    {
      "MSG": checker ? "OK" : "NOT OK"
    }
  )).setMimeType(ContentService.MimeType.JSON)
}