Questions tagged [google-sheets-custom-function]
Use this tag for questions about custom functions in Google Sheets created by using Google Apps Script.
-2
votes
0answers
12 views
Including insignificant zeroes at the beginning of numbers of varying length
I am trying to scan numbers into google sheets spreadsheet, but some of these numbers begin with a zero. Currently, my spreadsheet will automatically delete the zeroes, but I want to keep them. Plain ...
0
votes
1answer
10 views
How to reference a Google Sheet by filename?
How to reference a Google Sheet by filename?
I would like to use IMPORTRANGE function, but it can only reference a file by URL not by filename.
Example filename:
2018-11.xlsx
2018-12.xlsx
0
votes
0answers
13 views
How to parse custom date format in Google Sheets?
I often have sheets with dates written in different formats than the one GS recognizes as a date. What's a painless way to convert those to dates, perhaps by specifying a format string?
So to clarify,...
0
votes
0answers
8 views
Automatically import a range from a different Google Sheet at HOURLY intervals
In a google sheet, I have a dynamically changing cell that updates production every few minutes.
Each hour, I want to pull the new number to my alternative sheet, so that I may get the total ...
0
votes
0answers
15 views
Is there a way that I can flag in Google Sheets when multiple cells contain the same values?
I'm wondering if someone can help me in my battle with Google Sheets. I'm creating a rota and I want it to flag if all senior members of staff are either off or all on the same shift (either Early or ...
0
votes
1answer
12 views
Alternate to nested if statements (alternate posing of question)
Sample Data Sheet: https://docs.google.com/spreadsheets/d/1EIQcpzwdaoNSKEPu-F1NCUOtEjS6Ez0P73dmotClT7Q/edit?usp=sharing
The end goal is to have a Month + Year show up in a single cell, based on the ...
0
votes
1answer
22 views
Alternate to Nested IF statements
Goal here is to have the sheets search a row, then once it finds the first instance of >=0, return the cell value that contains the month+concatenate the value in the cell next to it (or any other ...
1
vote
0answers
14 views
How to Get Data from Multiple Cells and Combine into a Comment in another cell
I have a spreadsheet of orders (rows) that has room for 70 products (columns). I would like for a script to find every cell that has a value (0's are blank) and then write that value (this is the qty ...
1
vote
2answers
34 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
426 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
285 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
13 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
26 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
2k 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
21 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
1answer
280 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
88 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
133 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
1k 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
61 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
3k 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
1answer
256 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
692 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
638 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
623 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
428 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
823 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
641 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
2k 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
425 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
1k 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 ...
37
votes
11answers
42k 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:
...
