How to get the url images of twitter api and put in googlesheet?

Question

my code works in appscript with the GET call with fetch, then fetch gives me an array with different data nested, my code destructuring the data to get what I need, and put in a new array, each array with the data of each tweet, then this new array is pushed in the mainArray and so on with each tweet, after that, I use the SetValues to put each arrayData in each row and each data separate with columns in a doc of Google sheets

The code work for the link, tweet, username, date, like, comment and share. But I want to add the URL or preview_url in a new column if the tweet has an image o video, or if have a media_key, if the tweet has multiple images I only want the first one.

This is the code I use to manage all the data from the GET of Fetch

if(huboResultados){
const values = () => {
    const {data, includes} = datos
    let arrayData = [] 
    let arrayMetricas = []

    data.map(item => {
        const { author_id, id, text, created_at, public_metrics} = item 
        const {retweet_count, reply_count, like_count} = public_metrics;
        const totalinteracciones = retweet_count + reply_count + like_count 
        const idTweet = id;

        Logger.log(public_metrics)

        includes.users.map(user => {
            const { id, name, username} = user

            if(author_id === id){
                arrayData = ["twitter.com/anyuser/status/" + idTweet, text, username, created_at, like_count, reply_count, retweet_count, totalinteracciones]
            }
            return arrayData
        })
        
        Logger.log(mainArray)
        return mainArray.push(arrayData)
    })   
}


values()

var totaltweets = mainArray.length

Logger.log(query)
Logger.log(totaltweets)
Logger.log (activesheet.getLastRow())
activesheet.getRange("B10:I"+ activesheet.getLastRow()).clearContent()
activesheet.getRange(10,2,totaltweets, mainArray[0].length).setValues(mainArray);
}
}

And this is an example of the type of data I get from Fetch:

{
    "data": [
        {
            "text": "RT @PabloDa65125285: Oye #TataMartino porque No te quieren en La Selección:\n#FueraTata #SeleccionMexicana #Qatar2022 #Colombia #Chiringuito…",
            "public_metrics": {
                "retweet_count": 4,
                "reply_count": 0,
                "like_count": 0,
                "quote_count": 0
            },
            "created_at": "2022-09-28T14:59:33.000Z",
            "author_id": "1297299916654608389",
            "id": "1575138146433499138"
        },
        {
            "attachments": {
                "media_keys": [
                    "7_1575137009743659009"
                ]
            },
            "text": "@miseleccionmx Ya tiene chamba asegurada el Tata en la sección de \"Quién es quien en las mentiras\" \n#QuienEsQuienEnLasMentiras \n#AMLOBurlaMundial \n#SeleccionMexicana \n#MasAccionMasDiversion https://  t.co/73jOxVjydh",
            "public_metrics": {
                "retweet_count": 0,
                "reply_count": 0,
                "like_count": 0,
                "quote_count": 0
            },
            "created_at": "2022-09-28T14:55:07.000Z",
            "author_id": "974663527104356352",
            "id": "1575137031843373063"
        },
        }
     ],
    "includes": {
        "users": [
            {
                "id": "1297299916654608389",
                "name": "Juanma76",
                "username": "Juanma7611"
            },
            {
                "id": "974663527104356352",
                "name": "Ro2 ",
                "username": "ro2var"
            },
        ],
        "media": [
            {
                "media_key": "7_1575137009743659009",
                "preview_image_url": "https://pbs.twimg.com/ext_tw_video_thumb/1575137009743659009/pu/img/qluOCv0Uw_u2J_SG.jpg",
                "type": "video"
            },
            {
            "media_key": "3_1575014644439212032",
            "type": "photo",
            "url": "https://pbs.twimg.com/media/FduTeW3WYAAEm7W.jpg"
            }
        ]
    },
    "meta": {
        "newest_id": "1575138146433499138",
        "oldest_id": "1575088073242574849",
        "result_count": 10,
        "next_token": "b26v89c19zqg8o3fpzblrml2r91pb8ibeaxwgyo2ee8ot"
    }
}

Thank you for reading, and sorry for the bad english

Answer 1

I believe what you want is to get the media URLs inside of includes. Please try the following for the first part of your code:

var mainArray = [];

const values = () => {
    const {data, includes} = datos;
    let arrayData = [] ;
    let arrayMetricas = [];

    var mapImages = {};

    for (var i=0;i<includes.media.length;i++){
        if (includes.media[i].preview_image_url != undefined ){
            mapImages[includes.media[i].media_key] = {"preview_url": includes.media[i].preview_image_url}
        }
        if (includes.media[i].url != undefined ){
            mapImages[includes.media[i].media_key]  = {"url":includes.media[i].url}
        }
    }

    data.map(item => {
        const { author_id, id, text, created_at, public_metrics} = item 
        const {retweet_count, reply_count, like_count} = public_metrics;
        const totalinteracciones = retweet_count + reply_count + like_count 
        const idTweet = id;
        var preview_url = ""
        var url = ""
        if (item.attachments != undefined){
          for (var i=0;i<item.attachments.media_keys.length;i++){
            if (mapImages[item.attachments.media_keys[i]] != undefined){
              if(mapImages[item.attachments.media_keys[i]].url != undefined){
                url = mapImages[item.attachments.media_keys[i]].url
              }
              else if (mapImages[item.attachments.media_keys[i]].preview_url != undefined){
                preview_url = mapImages[item.attachments.media_keys[i]].preview_url
              }
            }
          }
        }

        Logger.log(public_metrics)

        includes.users.map(user => {
            const { id, name, username} = user

            if(author_id === id){
                arrayData = ["twitter.com/anyuser/status/" + idTweet, text, username, created_at, like_count, reply_count, retweet_count, totalinteracciones, url, preview_url]
            }
            return arrayData
        })


        return mainArray.push(arrayData)
    })   
}

values()

Edit: included mapImages to gather to mappings of media_keys that will need to be looked up in each of the tweets