Google Sheets ImportJSON – Preserve Existing Data

google sheetsimport

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?

Best Answer

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);
      }
    }
  }
}
Related Topic