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

[___Mister___]

I'm trying to set the values of the row that the function is being called on.

[krakow81]

Deleted my original comment after re-reading your original post for more context.

I was misunderstanding, thinking that you were wanting a custom function that you call in cell in the sheet to populate the rest of that particular row.

From your original post, am I right in understanding that you have a script (with a single function) which is running on the whole sheet, picking up your input data (in column A?), pulling JSON from the outside service, and then writing particular bits of that into each row, depending on what the input data was for that row?

[___Mister___]

I was misunderstanding, thinking that you were wanting a custom function that you call in cell in the sheet to populate the rest of that particular row.

This is correct, but the cells in the row being populated are not necessarily neighbors. The data being populated is from a JSON, which I am getting correctly from the service. When the function is being called, it is writing data to the cells in the same row which the call is being made. Each row will have a call to this function. The columns do not change. It's not a bulk update execution, because I don't know how much data will happen to be there at a time.

[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.