I'm trying to create a custom function in google App scripts where I converts some selected cells into a JSON text string. The function works when I run the macro but I can't get it to work as a custom function in the spreadsheet such that it would return the text string in the cell. I'm getting an error that says:
"Exception: Cannot call SpreadsheetApp.getUi() from this context. (line 1)."
This is strange because nowhere in my code am i calling SpreadsheetApp.getUi()
/**
* Returs JSON.
* @return Json formated data
* @customFunction
*/
function jsonify() {
// Get the active spreadsheet
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
// Get the active sheet
var sheet = spreadsheet.getActiveSheet();
// Get the selected range
var selectedRange = sheet.getActiveRange();
// Get values from the selected range
var values = selectedRange.getValues();
// Create an array to hold the JSON objects
var jsonArray = [];
// Get headers from the first row
var headers = values[0];
// Iterate through rows starting from the second row
for (var i = 1; i < values.length; i++) {
var row = values[i];
var jsonObject = {};
// Iterate through columns
for (var j = 0; j < headers.length; j++) {
jsonObject[headers[j]] = row[j];
}
// Add the JSON object to the array
jsonArray.push(jsonObject);
}
// Convert the array to JSON string
var jsonString = JSON.stringify(jsonArray, null, 2);
// Log the JSON string (you can modify this part based on your use case)
Logger.log(jsonString);
// Optionally, you can return the JSON string
return jsonString;
}