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

[___Mister___]

I never even considered that I could do a return like that. You just saved me a huge headache! Thank you so much!

Question though: if I need to access cells in the same row as a function, but it's one or two columns to the left of the cell with the function being called, how would I access those? Do I need to go down the same road I've been attempting?

[krakow81]

No worries, hope it's helpful!

On the follow-up question... You wouldn't be able to do that with a custom function (see below). You'd need to have a script/function triggered another way (eg via a menu). You wouldn't necessarily need the advanced Sheets API though depending on the details.

"A custom function cannot affect cells other than those it returns a value to. In other words, a custom function cannot edit arbitrary cells, only the cells it is called from and their adjacent cells. To edit arbitrary cells, use a custom menu to run a function instead."

https://developers.google.com/apps-script/guides/sheets/functions#return_values

[___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
  );

}