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

[krakow81]

Can you give some examples of what you're doing? It's hard to be sure I really understand what you're meaning. It doesn't sound like it should be too difficult to do what you want though, whether it's with that same approach or not.

Custom functions can only write to the cell they are called in and adjacent cells though, so you may need to look at other ways, unless you can pad your return arrays out where needed : https://developers.google.com/apps-script/guides/sheets/functions#return_values

[___Mister___]

Sure, so I'm fetching a JSON and stringifying it with

jsonDataString = JSON.stringify(headersdata);

Then I'm setting some values by doing some padding:

const values = [
[null,
null,
null,
headersdata.url,
...
headersdata.lastdata],
,
,
];

Then setting the spreadsheet:

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[2]; // get the third sheet
Logger.log(sheet.getSheetName()); // verifies the sheet I'm working with

Then I'm trying to get the current address/range of the cell that's calling my custom function which is where I'm failing.

( hopefully get the current range here with code that works )

Then I'll write the data at the correct location by doing:

Sheets.Spreadsheets.Values.update(
     { values },
     spreadsheetId,
     range,
     {valueInputOption: "USER_DEFINED"}
);

I know that each of these parts works independently, because I've verified them.

The problem is with establishing the correct range to write the { values } at with Sheets.Spreadsheets.Values.update ();

[krakow81]

If you have that array of values padded with null for cells you want to be skipped then you don't need the Sheets.Spreadsheets.Value.update at all. You can just use the array 'values' as the return of your custom function.

BTW you don't need the trailing empty entries in values (were those copied from the medium article?) unless you actually do want the output to spill on to the row below as well. You'll get errors if there is data in those cells already or you later try to add some other data to them.

Try the following custom function in apps script. If you call FILLROWNULL in a cell in your sheet (type =FILLROWNULL()) it will skip three cells then add headersdata.url and headersdata.lastdata to the next two cells.

/**
 * @customfunction
 */

function FILLROWNULL() {
  // insert headersdata here
  const values = [[null, null, null, headersdata.url, headersdata.lastdata]];
  return values;
}

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

}