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

The location layer on my Google Maps is not appearing

Enhancing 2D video viewing with Threejs interactivity

Tips for effectively grouping a JavaScript object array with the help of Lodash

What is the best method to eliminate empty columns while retrieving dynamic data in ion segments?

Smooth scrolling problems arise when a page loads and the offset jumps unexpectedly when the ID and hash are set to the same value

Accessing data attributes using jQuery and the class selector

Utilize Jquery to send a preset value through an ajax request

jqGrid - Error when the length of colNames and colModel do not match!

How can the entire menu be closed in Bootstrap 5 when clicking on a link or outside of the page?

Error with JavaScript slideshow The slideshow using JavaScript seems to

Socket.io is most effective when reconnecting

"Is it possible to load the jQuery dform plugin from a JSON file

Transform into dynamic types in Java

Verify that the text entered in the form is accurate, and if it meets the required criteria, proceed to the next

Stream JSON data to a file with Node.js streams

Testing for the presence of a child element within a parent component in React

Guide to attaching a mouse click event listener to a child element within a Polymer custom component

What are some React component libraries compatible with Next.js 13.1?

Tips for making SoapUI json requests compatible with German umlauts

Unable to delete React element by ID as it is undefined