The use of JSON.parse does not support parsing URL links

Question

The use of JSON.parse does not support parsing URL links

As a junior developer specializing in Javascript and Google Apps Script, I decided to enhance the functionality of my Google Sheets by tracking the last modification time of URLs stored in them. Although I attempted to create a script for this task, it seems that I may need some professional guidance.

The goal is to loop through a column containing around 2500 URLs and display each URL's modified date (extracted from its metadata) in an adjacent cell. Below is the code I have written:

    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 instance, consider the following link

Its metadata includes:

{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 am specifically interested in extracting only the 'Last-Modified' field from this metadata array and displaying it in the adjacent cell.

Thank you in advance for any assistance provided! This community has been incredibly helpful!

I have also attached a screenshot showcasing my current code and the debugger mode illustrating the sample links I am working with: https://i.stack.imgur.com/bWUD3.jpg

javascript json url google-apps-script

Answer 1

Answer №1

Upon reviewing the information on Google's documentation ( https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app#fetch(String) ), it appears that the data stored in the variable url is not in string format.

The function JSON.parse expects a string input to convert it into a JavaScript Object, Array, String, or other data type.

To correct this issue, you should replace JSON.parse(url) with JSON.parse(url.getContentText('utf-8')) as specified in the documentation here: https://developers.google.com/apps-script/reference/url-fetch/http-response

Answer 2

Upon reviewing the information on Google's documentation ( https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app#fetch(String) ), it appears that the data stored in the variable url is not in string format.

The function JSON.parse expects a string input to convert it into a JavaScript Object, Array, String, or other data type.

To correct this issue, you should replace JSON.parse(url) with JSON.parse(url.getContentText('utf-8')) as specified in the documentation here: https://developers.google.com/apps-script/reference/url-fetch/http-response

Answer 3

Answer №2

It took me a few days of dedicated work, but I finally succeeded in extracting the Last-Modified date value for each URL in my spreadsheet.

Here is the code I used:

function iteration1() { 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
    //Accessing the Google sheet
    var sheet = ss.getSheetByName("Sheet Name");
    //Fetching 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 getting its Properties into a new Array
          var url = UrlFetchApp.fetch(urls[row][col].toString());
          var tweets = url.getAllHeaders();

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

      //Extracting the modification date from the property meta-data & setting it as a String in the respective cell.
      sheet.getRange(row+2, 12).setValue(userProperties.getProperty(tweetsKeys[7]));
    }
}

Big thanks to everyone who helped out!

Answer 4

It took me a few days of dedicated work, but I finally succeeded in extracting the Last-Modified date value for each URL in my spreadsheet.

Here is the code I used:

function iteration1() { 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
    //Accessing the Google sheet
    var sheet = ss.getSheetByName("Sheet Name");
    //Fetching 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 getting its Properties into a new Array
          var url = UrlFetchApp.fetch(urls[row][col].toString());
          var tweets = url.getAllHeaders();

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

      //Extracting the modification date from the property meta-data & setting it as a String in the respective cell.
      sheet.getRange(row+2, 12).setValue(userProperties.getProperty(tweetsKeys[7]));
    }
}

Big thanks to everyone who helped out!

The use of JSON.parse does not support parsing URL links

Answer №1

Answer №2

Similar questions

Converting json file data into a case class using Spark and Spray Json

Who is the father of Bootstrap Popover?

Having trouble passing data between view controllers

Confirming whether the digit entered in jQuery is a number

Differences between Array and Database Search

Displaying svg files conditionally in a react native application

Removing a faded out div with Vanilla JavaScript

Failure to trigger the success action in ExtJS

Why isn't v-model functioning properly in Vue?

Developing a collection of reusable components in a Javascript bundle for enhanced efficiency

What is the best way to retrieve the value of an nth column in a table using

What is the best method for returning the AJAX outcome to the JSP page?

Leveraging grunt-develop

Set up specific vue.config.js configurations for unique environments in Vue

PHP Form encountering error due to JSON decoding following an AJAX request

remain on the multi drop down page for a specified duration

Alter the Vue view using an object instead of the url router

Moving a row from one Jquery Datatable to another table

Struggling to access specific data within a JSON object? Wondering how to extract and display data from a JSON object in VUE?

The watch functionality is failing to work within a specialized attribute directive that is being utilized within a separate custom element directive