Must-know Google Sheets tip for marketers: Add image creatives to your reports 🖼️

This is great tip, thank you for sharing @mariia.gurova! 🤗

@Mariia 👋🏼, one question, image addresses usually "expire". Does this solution work so that we don't have to download this address again and keep renewing this data?

Tks <3

@Sandrinha not sure if I get your question right 🤔 Could you please clarify. 

If you have the post URL and use the formula, image is “extracted” from there. And if you use Supermetrics connector to get the post URL, there’s no need to download anything 😊

When you use SM dimension “image url” you receive a temporary image link, as Facebook keeps changing the image directory. I understood your explanation that If you have the post URL and use the formula, image is “extracted” from there. But, the image extraction is done in sheets. Do you know how to “take” this image to Looker Studio?😊

 Hi @Sandrinha 

Are you using Looker Studio with the Supermetrics Looker Studio connector, meaning that the data isn't stored before landing in Looker Studio? If you're using this direct connection, there’s no need to save the image—Looker Studio will call the API, always pulling in the most current URL.

However, if you’re storing the data somewhere else before it reaches Looker Studio, you would need to capture the image during that storage step. As far as I know, there isn't a way to do this directly in Looker Studio. But if you’re using the direct connector, you shouldn’t need to take this extra step 😊.

Hi, @Milja 

I use Sheets to extract data with Supermetrics connectors and build my dashboard in Looker. By extracting the image addresses, I can view them for a few days until Facebook or Instagram change the image address again. I've tried converting the image address to base 64, so I can have a permanent image on my dashboard. However, since there are SO MANY of them, it's impossible to convert base 64 all the addresses.

Hi @Sandrinha 

Yes, this is expected behavior due to Facebook’s API, which causes image URLs to expire after a few days. Since Supermetrics only retrieves the live URLs directly from Facebook, any stored data will eventually contain broken image links if not refreshed frequently. While storing images persistently would address this issue, Supermetrics does not currently offer a feature to save image files in the data storage itself.

One workaround (though untested) might be to create a dedicated query in a separate tab in Google Sheets specifically for image URLs, with only the image ad ID and the URL itself. You could set a longer date range for this query—maybe up to a year, although very large ranges might lead to timeouts. You’ll need to experiment with the time range to determine how much historical data you can retrieve, but this could help display fresh URLs for the recent data.

Then, you can join the image URLs to the main data using the image ad ID and bring the combined table into Looker Studio. By refreshing Google Sheets queries daily, this could potentially keep the URLs updated in Looker Studio, allowing the images to stay visible.

Let me know if this solution works for you! 😊

Hi @Sandrinha I was able to get this working in my test sheet. Here's how I set it up:

I have two tabs:

1. Daily Data: Contains your daily data (including Ad IDs).
2. Image URL: Contains the Ad IDs and their corresponding image URLs.

To join the Image URL to the Daily Data based on the Ad ID, I used the following formula with ARRAYFORMULA:

=ARRAYFORMULA(IFERROR(VLOOKUP(E2:E, 'Image URL'!A2:B, 2, FALSE), "No Image Found"))

How it works:

• I placed this formula in a new column in the Daily Data tab, starting at row 2 (with "Image URL" as the column header in row 1).

  

   

• Using ARRAYFORMULA, I only need to write the formula once; it automatically applies to the entire column without the need to copy-paste it row by row.
• VLOOKUP searches the Ad ID in column E of the Daily Data and matches it with the Ad ID in column A of the Image URL tab.
• The 2 in the formula tells it to pull the value from the second column (the Ad creative image URL column).

  

   

• If there’s no match, IFERROR ensures that "No Image Found" is returned instead.

In my test sheet, the Ad ID is in column E of the Daily Data, and the Image URL table spans from A2 to B (without a fixed row height). This way, it can automatically handle any number of rows.

I hope you find this useful 😊