Custom Google App Script function not working: looking for Spreadsheet.getUI even though I'm not calling it

34 Views Asked by At

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;
}
0

There are 0 best solutions below