I'm trying to import JSON data to Google Sheets, but it imports JSON data as HTML.
my formulas are:
=INDEX(IMPORTDATA("https://instagram.com/"&B1&"/?__a=1"),1,1)
- where B1 is username
=VALUE(REGEXREPLACE(INDEX(IMPORTDATA("https://instagram.com/"&B1&"/?__a=1"),1,1),"[^[:digit:]]", ""))
<< this formula is pulling numbers from a string.
Sometimes it works and sometimes gives only "0".
When I try to open this link with chrome:
I tried another approach with the Google apps script, but it has the same problem: SyntaxError: Unexpected token < in JSON at position 0 (I suppose it gets "<" from HTML) I tried to stringify, but HTML has a different content, there is no instagram ID info. Please, help me how to get this data from JSON to Google Sheets: "logging_page_id":"profilePage_460563723"
function getID() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("insta");
var user_name = sheet.getRange("B1").getValue();
var IDcell = sheet.getRange("B3");
var base_url = "https://www.instagram.com/" + user_id + "/?__a=1";
var pID = parseInt(urlfetch(base_url)['/logging_page_id']);
//Logger.log(pID);
IDcell.setValue(pID);
};
function urlfetch(url) {
var ignoreError = {
"muteHttpExcecptions": true
};
//var url = "https://www.instagram.com/geomantiger/?__a=1";
var source = UrlFetchApp.fetch(url, ignoreError).getContentText();
var data = JSON.parse(source);
return data;
Logger.log(source);
}
P.S. I tried almost all suggested "import" functions:
importdata
importxml
importjson (custom library)
I researched a lot but didn't find a solution.