Questions tagged [google-sheets-custom-function]
Use this tag for questions about custom functions in Google Sheets created by using Google Apps Script.
0
votes
2answers
19 views
IF and THEN statements on spreadsheets using color
I am creating spreadsheets with multiple tabs for children to work through. To show they have completed each tab, when they have finished the work correctly I am using the following statements:
=IF(...
2
votes
1answer
11 views
Format cell, If word from list in cell
I'm trying to color tag cells according to keywords they may contain. I've searched just about everywhere, and have tried various applications of the Search and Find functions to no avail.
Imagine I ...
0
votes
1answer
27 views
Reference all tabs with a specific word in COUNTIF statement
Basically, I am trying to make a master tab that adds up all of the data from all the other tabs. These tabs are weekly breakdown numbers for ad statistic numbers, so there is a new tab added every ...
0
votes
0answers
23 views
Using custom function in QUERY in Google Sheets results in “unknown function:”
I have a custom function written in Google Script for Sheets named sheetnames(). It returns the names of individual sheets inside a worksheet. When used in a regular cell-like =sheetnames(), it ...
0
votes
2answers
37 views
How to extract hovered links from a cell in Google Sheets?
I have set up this demo spreadsheet.
Can someone please help me extract the URL?
0
votes
1answer
129 views
How to use custom function with arrayformula?
I'd like to use a Google Apps Script custom function in conjunction with arrayFormula so this may be applied to a range. How do I achieve this?
Let's say I have wrote a function that will return ...
1
vote
1answer
23 views
Copy formula every other row and every column
I have been playing with scripts to try and figure out this tedious task as there are 600ish rows in my sheet, times 4 sheets.
So far I have come up with this
function myFunction() {
var ss =...
2
votes
2answers
95 views
ARRAYFORMULA with IF, DETECTLANGUAGE and LEN
I'm trying to make an array formula to detect the number of English characters in a range.
When I use the following for individual cells:
=IF(DETECTLANGUAGE(ARRAYFORMULA(A5:A))="en",LEN(ARRAYFORMULA(...
1
vote
3answers
268 views
LEFT JOIN two and more tables in Google Sheets
Say I have two tables. Table A (material requirements):
product_id | material | required
1 | A | 0.5
1 | B | 0.7
2 | A | 0.2
3 | A | 0....
0
votes
0answers
21 views
Send email notification when a data in column matches a certain value
I am trying to make Google Sheets send a notification to a particular person/s when one of the fields on column Q is equal to the word "High". I want the email to be sent when it is high because it ...
1
vote
3answers
43 views
Identify max value, divide others by half, then total all
This may be too specific of a formula, but I'm hoping there is a function or script that will accomplish it.
I need a Google Sheet to identify the largest number in a row and divide all of the OTHER ...
0
votes
1answer
253 views
Split multi-line (comma-separated) cell into new rows + duplicate surrounding row entries
Based on: Google sheets split multi-line cell into new rows (+ duplicate surrounding row entries)
How can I rewrite this for use of a comma as separator and remove blank spaces?
Input sheet:
...
1
vote
1answer
52 views
Custom Script in Pivot Table Value Formula = Error Loading Data forever
I am very new to the Google Sheets platform, but I am not new to programming, and am comfortable creating and managing javascript code, and reading API documentation.
I am working on a side project, ...
0
votes
2answers
399 views
Having Google Sheets not recalculate a formula on page load
I have a Google Sheet with a list of addresses, and a column that geocodes the addresses with mapquest via Google App Script. This works great, except it "recalculates" the addresses on page load. ...
0
votes
1answer
400 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
61 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
33 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
26 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 ...
1
vote
0answers
21 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 ...
0
votes
0answers
14 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
1k 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
737 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
28 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
44 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
5k 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
45 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
349 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
272 views
Is there a way to get MD5 Checksum from the files with a custom formula?
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 ...
0
votes
0answers
182 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
2k 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
173 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
5k 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
407 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
1k 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
930 views
How to make that a custom function update its result immediately
I'm using a custom function 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 of ...
0
votes
0answers
739 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
525 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
6k 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
983 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
734 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
432 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 ...
42
votes
12answers
47k 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:
...
