2

I'm using the 3rd party importJSON() function, and I needed a way to make it calculate only when I explicitly requested it. Thanks to this question, I now have a way to do that, but a big problem remains.

Now, any time a calculation is performed (which is On Change), importJSON either calculates if I have it set to do so, or wipes out the existing data that was calculated last time. This means my data is constantly disappearing on me in the middle of trying to use my spreadsheet.

Is there any way to make importJSON() return if not set to calculate, but not erase data in previously-calculated cells?

1

Generally, multiple import-statements of whatever kind are a path to frustration. I offer a general solution, not specific to ImportJSON: it is a way to always invoke a specific custom function explicitly, using the menu. The code is below, and here is the explanation.

  1. Instead of ImportJSON, use manualImportJSON functions, with the same parameters.
  2. This function won't import anything on its own, it only prints "Imported data below this cell" in the cell.
  3. Have ImportJSON in the script as it originally was, but it will no longer be called as a custom function. For the sake of completeness I included a demo version of this function here; it only spits out the parameters and a few numbers to show that it works.
  4. To actually import data, use "Actually import JSON" item in the custom menu, which is added by the onOpen function. It calls the function actuallyImportJSON, which finds all manualImportJSON functions in the sheet, retrieves parameters from it, and passes them to the real ImportJSON function. Then it puts the imported data in the sheet, below the cell with manualImportJSON, so as not to overwrite it. (Note: the data will mercilessly overwrite anything, unlike the output of custom functions.)

Specifically, /^=manualImportJSON/i.test(formulas[i][j]) is the part that tests for "manual" function and eval("ImportJSON" + formulas[i][j].slice(17)) is the part that evaluates the real ImportJSON function. The number 17 is just the length of =manualImportJSON, the characters that are being replaced by ImportJSON.

Limitations of this approach: (a) the arguments of custom function must be literals, not formulas or cell references; (b) the custom function cannot be a part of formula. See below for an alternative.

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.addMenu("Custom", [{name: "Actually import JSON", functionName: "actuallyImportJSON"}]);
}

function ImportJSON(url, query, parseOptions) {
  return [[url, query, parseOptions], [1, 2, 3], [4, 5, 6]];
}

function manualImportJSON(url, query, parseOptions) {
  return "Imported data below this cell";
}

function actuallyImportJSON() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var formulas = range.getFormulas();
  for (var i = 0; i < formulas.length; i++) {
    for (var j = 0; j < formulas[0].length; j++) {
      if (/^=manualImportJSON/i.test(formulas[i][j])) {
        var data = eval("ImportJSON" + formulas[i][j].slice(17));
        sheet.getRange(i + 2, j + 1, data.length, data[0].length).setValues(data);
      }
    }
  }
}

Cell references/formulas as arguments

If the original custom function had such parameters, the approach above will not work since a JavaScript function has no idea what "B2" or such mean. Here is a modified approach.

  1. Instead of =manualimportJSON(url, query, parseOptions), type only the string manualImportJSON in a cell, and put its parameters in separate cells to the right of it. (So, if B2 has "manualImportJSON", then C2 has the URL, D2 has the query, and E2 has parse options.)

  2. Use the modified version of actuallyImportJSON below.

This version gets all values from the spreadsheet, rather than formulas. If a value matches "manualImportJSON" (case insensitive) than the contents of three cells to the right are passed as parameters to ImportJSON; the rest goes as above.

function actuallyImportJSON() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();
  for (var i = 0; i < values.length; i++) {
    for (var j = 0; j < values[0].length; j++) {
      if (/^manualImportJSON$/i.test(values[i][j])) {
        var data = ImportJSON(values[i][j+1], values[i][j+2], values[i][j+3]);
        sheet.getRange(i + 2, j + 1, data.length, data[0].length).setValues(data);
      }
    }
  }
}
  • Aha... I think I follow, that's pretty clever. It will take me a bit to get my head around implementation of this in my sheet but I will take a stab at it and post back once I do. Thanks! – Jonathan van Clute Aug 19 '17 at 0:24
  • Sadly I can't get the custom menu to work. In fact I also just noticed that the custom menu in the real importJSON script, also does not work. There are no custom menus at all in my sheet... no idea why at the moment. – Jonathan van Clute Aug 19 '17 at 0:39
  • Yeah I did, several times. No dice. Same is true for the menu that is added during onOpen in importJSON as well. In fact I never even knew it was supposed to add a menu until I noticed it now, because it never has done so. Maybe a Sheets issue...? Dunno. – Jonathan van Clute Aug 19 '17 at 0:56
  • OK I got everything going, problem was a conflict with trying to merge your new script file with my existing one. Left them separate and got it all working. Problem now I can't figure out. My sheet does calculations in two columns, B and K. Column K concatenates part of its API URL from cell J. This is causing your script to throw ReferenceError: "J2" is not defined. J2 is definitely defined, so I can't make out why this is happening. Any ideas? – Jonathan van Clute Aug 19 '17 at 17:30
  • oh FYI it works fine if I place the value of J2 directly in the formula in K2, rather than concatenate. I have a feeling your script may be treating the cell formula as a pure literal string, thus getting confused by the reference to another cell. – Jonathan van Clute Aug 19 '17 at 17:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.