How can I automatically populate a cell in Google Sheets with an image from a Pinterest page?

Question

Let's say I have a Google Sheet with URLs to individual pins on Pinterest in column B. For example: https://www.pinterest.com/pin/146578162860144581/

I'd like to populate cells in column C with the main image from the URL in column B.

Currently I have to do this manually by clicking through to the URL in column B, copy the image URL, and insert image into the cell in column C.

Is there a way to automate this?

Answer 1

Solution

Yes, you can achieve this using the sheet's Google's Apps Script. Below the following piece of code you can find a brief explanation on how this works.

The code

function populateImage() {  
  var sheet = SpreadsheetApp.getActiveSheet();
  // Get cell values of your link column
  var values = sheet.getRange('B1:B5').getValues();
  // Range where we want to insert the images
  var imgrange = sheet.getRange('C1:C5');
  
  for (i=0;i<5;i++){
    // Cell we want to insert the image
    var cell = imgrange.getCell(i+1, 1);
    
    // Pin Id which is at the end of each url provided, in this case 146578162860144581
    var number = values[i][0].substring(29,values[i][0].length-1);
    
    // Url to make the fetch request to access the json of that pin
    var url = 'https://widgets.pinterest.com/v3/pidgets/pins/info/?pin_ids='+number;
    var response = UrlFetchApp.fetch(url);
    var json = response.getContentText();
    var data =  JSON.parse(json);
    
    // Url of the image of the pin
    var imgurl =data.data[0].images["237x"].url;

    // Insert image from url
    cell.setFormula('=image("'+imgurl+'")');
  }

}

Result

Explanation

To insert an image in a website in your sheet you will need the image url. This is the most tricky part in this issue. Pinterest does not provide a good way to get this image url by just fetching to the pin url as this request will return HTML data and not json. To achieve this you will need to make a fetch request to this url https://widgets.pinterest.com/v3/pidgets/pins/info/?pin_ids=PINIDNUMBER. You can find more information about this in this Stack Overflow question, credit goes to @SambhavSharma .

When you fetch following this url you will get the pin's json from which you can retrieve your desired image url (apart from many other data about this pin). With it you can simply insert it in the next column.

I hope this has helped you, let me know if you need anything else or if you did not understand something.