Google script get json data from spread sheet

Question

I am making a telegram bot by using google script app with google sheet. Now,I had linked the telegram update data to my google sheet by following the step on youtube tutorial,which means everytime someone send messages to the bot,my google script app will update a json code to the sheet automatically. Like this:enter image description here

What I want to do now is to get the "id" and "text" from the json code in the sheet,anyone can help me?

Show the code you have so far. – ziganotschka Mar 31 '20 at 13:14 — ziganotschka, Mar 31 '20 at 13:14

score 0 · Accepted Answer · answered Mar 31 '20 at 18:18

Try this:

function bbstestbot() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getSheetByName('bbstestbot');
  const rg=sh.getDataRange();
  const vs=rg.getValues();
  vs.forEach(function(r,i){
    let js=JSON.parse(r[0]);
    let id=js.message.message_id;
    let text=js.message.text;
    sh.getRange(i+1,2).setValue(id);
    sh.getRange(i+1,3).setValue(text);
  });
}

Google script get json data from spread sheet

1 Answers1