Questions tagged [google-sheets-custom-function]
Use this tag for questions about custom functions in Google Sheets created by using Google Apps Script.
1
vote
2answers
26 views
Using multiple AND conditions in one IFS fromula
I’m trying to make sheets calculate my delivery times based on which locations the items are shipped between.
Tried combining the Ifs and AND formulas, but I keep getting errors.
(Let’s say the ...
0
votes
0answers
12 views
Count Seconds on Sheet Update
When you change any value in a cell, some functions automatically generate new update. One of these functions is NOW().
I currently have =SECOND(NOW()) in A2. I also have a checkbox made with data ...
1
vote
2answers
143 views
Multiple IF statements: IF, AND, ISDATE, THEN formula
I'm trying to get a nested formula to work, but I keep getting an error. I tried using the generic formula =IF(AND(A1="this",B1="that"),"x","") but couldn't figure out how to make sure Q6=date. I had ...
1
vote
1answer
117 views
Conditional Formatting COUNTIFS (Google Sheets)
I'd like column P to be highlighted when:
A contains "x"
E contains "approved"
O < today (date has passed)
P and Q are blank
My formula doesn't work:
=COUNTIFS(A6:A, "x", E6:E, "approved", ...
0
votes
1answer
8 views
Conditional Formatting IF/AND (Google Sheets)
I'm trying to highlight cells in column N when the following criteria are met:
K = number (don't count text or blanks)
N = blank
L = date
Column K is formatted as a number
The formula I used: =AND(...
0
votes
0answers
24 views
How to auto date on cell update in Google Sheets
I'm using the following script in Google Sheets. It works well but I want to limit it to a specific tab rather than every tab in the book.
function countBackground(color, inputRange) {
var ...
0
votes
2answers
878 views
How to format the time in this Google Apps Script
I want to format a Google Sheet cell when I read it in my script but it comes out 25 minutes later.
The cell's time is 20:00:00 but the output is "20:25". I am using this
var formattedDate = ...
0
votes
0answers
18 views
Google Sheets onEdit automation and separating strings in a cell
I'm compiling a google sheet with a list of Form Responses after answering questions on a google form. This is what the google From Response Page would look like:
I want to compile a script so that ...
0
votes
0answers
240 views
Google sheet - get crypto currency price from Cryptobridge Exchange API
How can I retrieve only the price from this API in a Google spreadsheet? This is the API ticker - https://api.crypto-bridge.org/api/v1/ticker
For example, if I use this function
=INDEX(IMPORTDATA(...
1
vote
2answers
56 views
I have a google sheet full of important links to images on google drive. Is there a way to get MD5 Checksum from the files with a custom formula?
Here is my scenario:
No. | PHOTO | MD5 CHECKSUM FROM PHOTO FROM G-DRIVE |
01 - LINK TO PHOTO 1 - MD5 CHECKSUM 1
02 - LINK TO PHOTO 2 - MD5 CHECKSUM 2
03 - LINK TO PHOTO 3 - ...
0
votes
0answers
102 views
How can I continue a series within a formula
I have a custom function I'm using in Google Sheets that follows the following command order: VAR('API-reference','API-variable','date','refresh-cell'). I am wanting to autofill the date so that I can ...
0
votes
1answer
712 views
Adding multiple rows by script
I have this simple Google Sheets script that adds a row after every already existing one. What do I have to change to for example add 12 rows?
function addRows(){
var sheet = SpreadsheetApp....
1
vote
1answer
47 views
How to get new Google Form responses to take on non-form functions in Google Sheets
I developed a Google Form that copies responses to a linked output spreadsheet in Google Sheets.
In addition to the columns created by the form question/responses themselves, I've added additional ...
2
votes
1answer
2k views
Google Sheets Filter Multi Column List With Another List Using A Function
I have two lists (A and B). I want to filter list "A" (a multi column list) with values in list "B" to create a Final List, list "C".
List "A" has multiple records for each student ID number. List "B"...
0
votes
0answers
207 views
Steam Webapi & Hours played for a specific game - Google Sheets
So I have managed to get a list of owned games and total hours in each game with =importJson. The only issue is I only want data for one game. The json query returns all owned games. The api wiki says ...
1
vote
1answer
566 views
Google Sheets casting cell value from Object to Number
One cell in a Google Sheets document holds a value given by a SUM of cells formatted as "elapsed hours":"elapsed minutes". Attempting to make calculations in a custom script on this cell will throw ...
0
votes
2answers
557 views
Function to return sheet tab title not updating
I'm using the following script to read the sheet name (shown in the tab) and then return the name to the cell in the summary page. The script works, however it doesn't update when I change the name ...
0
votes
0answers
557 views
Why am I hitting the limits of urlfetch?
In Google Sheets, I'm manually invoking the 3rd party importJSON() function for a few cells. There's absolutely no way that I can see, that I can be exceeding the 20,000 requests-per-day quota as I ...
0
votes
1answer
390 views
How to Process Rows of Google Sheet Singly?
I have a google sheet with the following headers:
| name | prop_id | number | street | city | state | zip | phone | first_name | last_name | email |
|--------|:--------:|--------:|-------:|-----:|...
5
votes
1answer
4k views
Detect whether a cell has strikethrough format
Is there a function that checks if the strikethrough format has been applied to the cell contents? I would like use a formula similar to (=IF(HAS_STRIKETHROUGH(C5), 1,0)
1
vote
1answer
775 views
Getting values from other sheets with a custom function
I've defined the following function, and use it in sheet1:
function myfcn (sheetname,rowstart,rows,columnstart,rows){
var sheet= SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetname);
...
6
votes
1answer
597 views
Test Google Spreadsheets Add-On with custom functions
I have set up a Google Apps Scripts project with the intent of creating a Google Spreadsheets add-on with custom functions. However, when I use the option "Test as add-on", I can't use the functions ...
2
votes
1answer
1k views
Is there any reliable alternative to IMPORTRANGE()?
After some time the formula breaks, and shows Error: Loading data..., and it won't work until I change anything in the parameters, e.g. Sheet1!a3:Ag to Sheet1!a3:AG, then it works for a few days, and ...
6
votes
1answer
420 views
Non-deterministic parameters to custom function
I have a problem with Google Spreadsheets: I create a custom function using the Script Editor and it only runs OK if the parameters I pass are known (constant) values.
If the parameters are non-...
4
votes
2answers
967 views
How do I reference existing formulas from custom formulas in Google Spreadsheet Scripts?
I want something essentially like this:
function contains(arg1, arg2) {
return NOT(ISERROR(search(arg1, arg2)));
}
But NOT and ISERROR are undefined even though using =ISERROR(...) works in the ...
36
votes
11answers
40k views
How to pass a range into a custom function in Google Spreadsheets?
I want to create a function which takes in a range... something like:
function myFunction(range) {
var firstColumn = range.getColumn();
// loop over the range
}
A cell would reference it using:
...
