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)
}
}```

CodePudding user response：

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);
}

CodePudding user response：

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:

• map()
• setValues(values)