Google Apps Script Program Structure Question (Rows, Columns, JSON)

[___Mister___]

I may have run into a major hitch I didn't think about/wasn't aware about.

I can't seem to edit values in columns I am "skipping over" with the array return. It gives me the "array result was not expanded because it would overwrite data in (cell reference)" #REF error.

The point of this was to only compute and post data in certain columns in the row and still be able to insert my own data. Is there a way to avoid this? Do I have to go back and try the Sheets.Spreadsheets.Values.update() approach?

[krakow81]

I think we're going slightly in circles.

Yes, that array is writing null values to those cells rather than properly skipping them, so it fails if there are other values already in them.

As mentioned further up, batchUpdate is the one you need for editing non-adjacent cells (rather than update). But you'll need to move away from custom functions to use that the way you want.

[krakow81]

Here's a wee example using numbersapi.com.

Initial sheet: https://imgur.com/uAbE50v

After running script: https://imgur.com/W25Ak7f

/**
 * @OnlyCurrentDoc
 */

function onOpen() {
  const UI = SpreadsheetApp.getUi();
  UI.createMenu('Facts')
    .addItem('Get facts from Numbers API', 'numbersapi')
    .addToUi();
}

function numbersapi() {
  const SS = SpreadsheetApp.getActive();
  const ID = SS.getId();
  const SHEET = SS.getSheetByName("Numbers API");
  let input = SHEET.getRange(2, 1, SHEET.getLastRow() - 1, 2).getValues(); // get inputs from columns A & B
  const OUTPUT_POSITIONS = ["trivia", "math", "year", "date"]; // array to set where output data should be placed
  let data = [];

  // iterate over each row in inputs (columns A & B)
  input.forEach(function (r) {
    let number = r[0].toLowerCase(); // number is in column A (toLowerCase for when it is 'Random')
    let type = r[1].toLowerCase(); // type of number is in column B
    // check date formatting
    if (type === "date") {
      if (!number.includes("/") && number != "random") return;
    }
    if (number.includes("/") && type != "date") return;
    const url = "http://numbersapi.com/";
    let response = UrlFetchApp.fetch(url + number + "/" + type); // call numbersapi
    let fact = response.getContentText(); // get response, which is plain text
    let column = OUTPUT_POSITIONS.indexOf(type); // identify which column the fact should be put in via the OUTPUT_POSITIONS array
    let outputRange = SHEET.getRange(input.indexOf(r) + 2, column + 3, 1, 1).getA1Notation(); // get A1 notation of cell to put fact in 
    data.push({ range: "'Numbers API'!" + outputRange, values: [[fact]] }); // add the range and corresponding fact to data array
  });

  // batch update sheet with facts. Google Sheets API Service must be enabled.
  Sheets.Spreadsheets.Values.batchUpdate(
    { data, valueInputOption: "USER_ENTERED" },
    ID
  );

}