Tomer Levi Tomer Levi - 1 month ago 6
Javascript Question

JSON.parse doesn't work with url links

I'm a junior javascript/google-apps-script developer and I wanted to add some functionality to some of my worksheets at Google Sheets. I'm working with many URLs and need to track what time they were last modified by the author.

I've built some script which I thought will work but apparently (after some reading) needs some professional touch.

The idea is to iterate through a column of URLs (2500~) and output each URL's modified date (from its meta-data) into a cell from the right. Here is my code:

function iteration1() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
**//The list to iterate on.**
var sheet = ss.getSheetByName("Fund List");
**//The column of which the links are stored**
var urls = sheet.getRange("D2:D150").getValues();

for (var row = 0; row < urls.length; row++) {
for (var col = 0; col < urls[row].length; col++)
**//Varifying if there is a URL within the cell**
if (urls[row][col] != '') {
**//Storing each URL in a new array**
var url = UrlFetchApp.fetch(urls[row][col].valueOf());
**//Parsing the meta-data of the URL into an array**
var tweets = JSON.parse(url);
**//Retrieve the link modification date from the meta-data array & outputs to the cell from the right respectivley.**
sheet.getRange(row+2, 13).setValue(Logger.log(tweets[4][2]).getLog());
}
}
}


For Example: the link http://documents.financialexpress.net/Literature/37773008.pdf

Its meta-data is:

{Accept-Ranges=bytes, X-Robots-Tag=noindex, nofollow, noarchive,nosnippet, Cache-Control=max-age=604800, Server=Microsoft-IIS/7.0, ETag="01827159b1d11:0", Access-Control-Allow-Origin=*, Access-Control-Allow-Methods=GET,PUT,POST,DELETE,OPTIONS, Last-Modified=Wed, 18 May 2016 23:00:00 GMT, Content-Length=113029, Access-Control-Allow-Headers=Content-Type, Date=Thu, 01 Sep 2016 11:43:52 GMT, Content-Type=application/pdf}


I only need the Last-Modified field Date within this meta-data array and output it to the cell from the right.

Thanks in advance for the helpers! great community here!

I have added a screenshot of my current code and the debugger mode which gives an example of the links I'm working on:
enter image description here

Answer

After few days working on it, I have managed to retrieve the value for the Last-Modified date key per each URL within my sheet.


My code:

function iteration1() { 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
    //The Google sheet to access
    var sheet = ss.getSheetByName("Sheet Name");
    //The array of URLs to check
    var urls = sheet.getRange("D2:D150").getDisplayValues();

    for (var row = 0; row < urls.length; row++) {
      for (var col = 0; col < urls[row].length; col++) {
        if (urls[row][col].toString() != '') {
          //Converting each URL to string and retrieving its Properties into a new Array
          var url = UrlFetchApp.fetch(urls[row][col].toString());
          var tweets = url.getAllHeaders();

          //Forming an array of Properties by Keys & Values
          var userProperties = PropertiesService.getUserProperties();
          userProperties.setProperties(tweets);
          var tweetsKeys = Object.keys(tweets);
        }
      }

      //Retrieving the link modification date from the property meta-data & outputs it as a String to the cell from the right respectivley.
      sheet.getRange(row+2, 12).setValue(userProperties.getProperty(tweetsKeys[7]));
    }
}

Thank you very much for your responses!