Cell google sheet as Json Source-CodePudding

I have a variable into Google Script for access to data through an API:

  var ticket_data = { "header_items": [{ "label": "Cliente", "type": "text", "item_id": "2cba1baf-207c-4529-ab72-7d35363983fc","responses": {"text": "PRUEBA: ANALISIS FALLA 2"},},],"template_id":"template_5f9b4f399fe342ce94fefada009ee467"}
  JSticket_data=JSON.stringify(ticket_data)

I can run UrlFetchApp.fetch() to access the API without error.

But if I store the variable data into a Google Sheet cell, and read the value like this:

  var ticket_data=sheet.getRange('B1').getValue()
  JSticket_data=JSON.stringify(ticket_data)

I get this value:

"{ \"header_items\": [{ \"label\": \"Cliente\", \"type\": \"text\", \"item_id\": \"2cba1baf-207c-4529-ab72-7d35363983fc\",\"responses\": {\"text\": \"PRUEBA: ANALISIS FALLA 2\"},},],\"template_id\":\"template_5f9b4f399fe342ce94fefada009ee467\"}"

UrlFetchApp.fetch() does not work correctly with that value.

I think that the problem is when I take the cell data by ticket_data=sheet.getRange('B1').getValue(), it take it as string, and then the JSON.stringify add a double quotes an then add the backslashes. I tried remove the backslash with replaceAll('\\','') but it did not work.

CodePudding user response：

The first code snippet in the question gets a JavaScript object and converts it to a text string with JSON.stringify().

The second code snippet in the question gets a text string that does not need to be processed with JSON.stringify() in order to get a text string. It is a text string.

In other words, you can simply use this:

  const ticketJson = sheet.getRange('B1').getValue();

...and use ticketJson as is in UrlFetchApp.fetch().

CodePudding user response：

Firs to all Thank you for your answer !! I'm sorry if I shoudn't write this update into the answer space. Please tell me where I must to do in the future

I'm sure I making something bad.

I have prepared the variable and parameters for UrlFetchApp as:

ticketJson = work_sheet.getRange('B1').getValue()
var params = {
    method:"POST",
    contentType:'application/json',
    headers:{Authorization:"Bearer " token},
    payload: ticketJson, 
    muteHttpExceptions:true
    };
var post_ticket = UrlFetchApp.fetch("https://api.website.io", params)
Logger.log(post_ticket)

the logger's answer is

Information {"statusCode":400,"error":"Bad Request","message":"Invalid request payload JSON format"}

but if I store the data into a variable and then excecute JSON.stringify() the results is OK.

var ticket_data = { "header_items": [{ "label": "Cliente", "type": "text", "item_id": "2cba1baf-207c-4529-ab72-7d35363983fc","responses": {"text": "PRUEBA: ANALISIS FALLA 2"},},],"template_id":"template_5f9b4f399fe342ce94fefada009ee467"}
JSticket_data=JSON.stringify(ticket_data) // if not excecute this, I get the same error that I tell before

I can't find where is the error Thanks again in advance