Google Apps Script : Get cell where the function i

问题:

I was sure to find that on StackOverflow but finally no. Maybe it's not possible?

I'd like to get the cell where my function is running.

If for example I have the function that is repeated in column D for line 1 to 10, I'd like that function to know that the current "processing" is D4 (for example).

I want this in order to be able to change the color of the cell based on the computation the function does. This, of course, changes based on the data input.

Is this possible? How? I wasn't able to find it.

Here's an applicable example code :

function ALERT_IF_NEGATIVE(input) {
    if (input < 0) {
        // Set that cell's color to red
    }
}

Thank you.

回答1:

Conditional formatting will solve this. https://support.google.com/docs/answer/78413?hl=en

This is an example I have from a spreadsheet I use:

In my code it adds the row values:

ss.appendRow([account,sAccount.name,sAccount.id,response.access_token,d,expTime,'=if(E'+lastRow+'<NOW(), "EXPIRED","VALID")',]);`

In this case the formula in column 'G' looks at a timestamp in Column 'E' and sets the value in 'G'. The conditional format looks at the values in 'G' and changes the cells background color based on the value.

The formatting is recalculated when there is a change to the sheet, or can be set-up to recalculate on a timer in File->Spreadsheet settings.