Question 9271 in Twitter API Question posted in
The official Twitter API documentation can be found here.

Problem in appscript, I can't accommodate the values of the twitter api well

I tried to pull data from the API of twitter and put in google sheet using appscript, with the next code was supposed that give to me 15 rows with different information in each column and it dont’s, only give me 1 row with the last tweet, anyone can help me? Or know a better method?

this the code:

function buscador() {
const res = UrlFetchApp.fetch("https://api.twitter.com/2/tweets/search/recent?query=pollo&max_results=15&expansions=author_id&tweet.fields=created_at", {
          headers:{
            Authorization: "Bearer......."
          },
        })


const results = res.getContentText()
var datos = JSON.parse(results)

var fecha = datos.data[0].created_at
var usuario = datos.includes.users[0].username
var tweet = datos.data[0].text
var id = datos.data[0].id

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Hoja5")

var headerow = ['fecha', 'usuario', 'tweet', 'id']

sheet.appendRow(headerow)

var row = [fecha, usuario, tweet, id]

sheet.appendRow(row)

for(var i=0;i<datos.data;i++){
  var row = [datos.data[i].created_at,datos.data[i].text,atos.data[i].id]

  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Hoja5").appendRow(row)
}
}```
Tags:

2

Answers


  1. 0

    I think you do it this way:

    function buscador() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Hoja5")
  const res = UrlFetchApp.fetch("https://api.twitter.com/2/tweets/search/recent?query=pollo&max_results=15&expansions=author_id&tweet.fields=created_at", {headers: {Authorization: "Bearer......."}})
  const results = res.getContentText();
  var datos = JSON.parse(results)
  var usuario = datos.includes.users[0].username
  var headerow = ['fecha', 'usuario', 'tweet', 'id']
  sheet.appendRow(headerow)
  let vs = datos.map(e => [e.data[i].created_at,usuario, e.data[i].text, e.data[i].id]);
  sheet.getRange(sheet.getLastRow() + 1,1,vs.length,vs[0].length).setValues(vs);
}
    Login or Signup to reply.
  2. 0

    Modification points:

    • About the next code was supposed that give to me 15 rows with different information in each column and it dont's, only give me 1 row with the last tweet, when I saw your script, I thought that the reason of your issue might be due to for(var i=0;i<datos.data;i++){. From your script, it seems that datos.data is an array. But, in this case, no loop is done. By this, I thought that only sheet.appendRow(headerow) is run. I thought that this might be the reason for your issue.

      • If you want to append the values using your script, it is required to modify it as follows.

          for(var i=0;i<datos.data.length;i++){

      • But, when SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Hoja5").appendRow(row) is run in a loop, the process cost will become high. Ref So, I would like to propose modifying as follows.

    Modified script:

    function buscador() {
  const res = UrlFetchApp.fetch("https://api.twitter.com/2/tweets/search/recent?query=pollo&max_results=15&expansions=author_id&tweet.fields=created_at", {
    headers: {
      Authorization: "Bearer......."
    },
  })
  const results = res.getContentText();
  var datos = JSON.parse(results);
  var usuario = datos.includes.users[0].username;
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Hoja5");
  var headerow = ['fecha', 'usuario', 'tweet', 'id'];
  var rows = [headerow, ...datos.data.map(({ created: { created_at }, text, id }) => [created_at, usuario, text, id])];
  sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, rows[0].length).setValues(rows);
}
    • By this modification, the values are put by one call.
    • In this case, when the script is run, the header row is added every run. If you don’t want to add it, please modify headerow.

    References:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search