CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Sai Krishna
Sai Krishna

Reputation: 135

Get SpreadSheetApp Id through webapp

I have Add-On and WebApp, I want to read active spreadsheetapp Id in webapp but not able to do it, getting error

SyntaxError: Unexpected token < in JSON at position 0

Kindly help me to correct the code as I am new to Javascript/JSON.

Add-On:

function onOpen(e) {
SpreadsheetApp.getUi()
      .createMenu('DataQ')
      .addItem('Submit to Manager', 'copyData')
      .addToUi();
}

//Backup data to master spreadsheet.  
function copyData() {
 var ss_id = SpreadsheetApp.getActive().getId();
  
  //This is the Web App URL.
  var url = "https://script.google.com/macros/s/xyz/exec";  

  var payload = {    
    "ss_id" : JSON.stringify(ss_id),    
  }

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

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

WebApp

function doPost(e) {

  var ss_id = JSON.parse(e.parameters.ss_id);  

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

  var ss_id = ss_id[0];
  
  var dss=SpreadsheetApp.openById('1vMeoANf6JJ8w0MP2DHJ6oTC4');
  var dsh=dss.getSheetByName('Consolidated_Data');

  dsh.getRange(dsh.getLastRow()+1,1,sData.length,sData[0].length).setValues(sData);

  var FrontDesk_ss = SpreadsheetApp.openById(ss_id);
  var FrontDesk_sheet = FrontDesk_ss.getSheetByName('Data');
  FrontDesk_sheet.getRange('D1:D20').setValue('Done');
  
  var response = {
    "status" : "SUCCESS",
    "sData" : sData,
  };     

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

}

Upvotes: 1

Views: 80

Answers (1)

Tanaike
Tanaike

Reputation: 201653

Modification points:

  • In your function of copyData(), ss_id is sent as "payload" : {"ss_id" : JSON.stringify(ss_id)},. In this case, at the doPost(e) side, e.postData.contents, e.parameters.ss_id, and e.parameter.ss_id are ss_id=%22{Spreadsheet ID}%22, ["\"{Spreadsheet ID}\""] and \"{Spreadsheet ID}\", respectively.
  • By this, when the value of Spreadsheet ID is retrieved from this event object, the double quotes of " are required to be removed.
  • But, when I saw your script of doPost, you use var ss_id = JSON.parse(e.parameters.ss_id). In this case, ss_id is dthe Spreadsheet ID. But, you use var ss_id = ss_id[0] after var ss_id = JSON.parse(e.parameters.ss_id). By this, ss_id is the 1st character of Spreadsheet ID. I think that this might be an issue of your script.

In this answer, in order to send and use Spreadsheet ID in your script, I would like to propose to modify both copyData() and doPost(e) as follows.

Modified script:

copyData()

Please modify copyData() as follows.

From: 
var payload = {
  "ss_id" : JSON.stringify(ss_id),
}
To: 
var payload = {
  "ss_id" : ss_id, // Modified
}

doPost(e)

Please modify doPost(e) as follows.

From: 
var ss_id = JSON.parse(e.parameters.ss_id);  

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

var ss_id = ss_id[0];
To: 
var ss_id = e.parameter.ss_id; // Modified

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

// var ss_id = ss_id[0]; // Removed

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. Please be careful this.

References:

Upvotes: 1

Related Questions