The problem: hyperlinks in Google Sheets can be in three different formats so if you have a script or add-on that relies on handling links in cells it can cause some headaches.
This article covers a thorough walkthrough of using Advanced Sheets Service to read hyperlinks from cells with different link formats.
Source: Everything a Google Apps Script Developer wanted to know about reading hyperlinks in Google Sheets … but was afraid to ask
This post covers a range of techniques and includes a typical pattern for writing structured data like JSON responses from third party sources.
Source: Google Apps Script Patterns: Writing rows of data to Google Sheets the V8 way