Questions tagged [google-sheets-custom-function]
Use this tag for questions about custom functions in Google Sheets created by using Google Apps Script.
92
questions
1
vote
2
answers
33
views
Google Sheets: Remove column headers from a QUERY()
I'm trying to eliminate (not hide) the column headings from the following QUERY(). The output from the =QUERY() must be in a single row.
=QUERY(myData!A:G, "Select SUM(E), SUM(F) WHERE D ='"&...
- 2,779
0
votes
1
answer
25
views
Google Sheets I need the column sums() of a table produced by a query()
I have a query() that outputs a table with two columns (A and B) of numbers. Pictured in IMAGE #1.
=QUERY(myData!A:G, "Select E,F WHERE D ='Books'")
IMAGE #1
But I need a function that ...
- 2,779
0
votes
1
answer
14
views
Can anybody please help me create a Query function to extract data from rows to columns (ignoring zero data) and using a drop-down box
I have only started to learn Google Sheets instead of Excel. I find it easier to understand if I have a problem to solve but the Query function quite difficult to understand.
I have table of fruit ...
0
votes
1
answer
15
views
Counting different types of text and producing specific text outcome
So I'm trying to create a tracker where I can gauge a pass/warning/fail status for each category. Under "Report Status", I wanted a function that can put out the text "Fail" if one ...
0
votes
1
answer
19
views
Trying to change Gantt chart in Google Sheets to highlight a cell based on date range and progress percentage
I have been working on a dynamic Gantt chart template in Google Sheets for awhile now. Here's how it currently looks:
As you can see, the task bar currently highlights gray dependent on the start and ...
- 21
0
votes
2
answers
104
views
Google Sheets sum-by-color function is no longer working. Possible to update?
Been using the following function to generate the sum of same-colored cells:
function sumColoredCells(sumRange,colorRef,test) {
var activeRg = SpreadsheetApp.getActiveRange();
var activeSht = ...
- 17
0
votes
1
answer
39
views
I am trying to find a custom function to find the min value of coloured cells ONLY for a range where some cells have a specific color
I am trying to find a custom function to find the min value of coloured cells ONLY for a range where some cells have a specific color.
The best I could find is to count the cells with a certain color.
...
- 1
2
votes
1
answer
315
views
How to pass multiple comma separated values in a cell to a custom function?
Suppose, there are values such as 1,A, 2,B, 3,D in certain cells. My intention is to add 1,2,3 (LHS) separately and A,B,D (RHS) separately. To achieve that, I have written certain custom function in &...
- 163
0
votes
0
answers
18
views
Reference same cell from multiple worksheets automatically with list going down? [duplicate]
I was wondering how can I make my main /master sheet more automatic so I don't have to input every data such as =sheet1!b2, =sheet2!b2, etc. I was thinking if there is some way to make it more ...
- 3
-1
votes
1
answer
43
views
QueryByName() function from another sheet in google spreadsheet
Is it possible to use the QueryByName() function using another sheet from the same spreadsheet?
=query(nor9609s!A1:Z500,querybyname(A1:Z1,"SELECT `Items_shopid` WHERE `Items_id`>0"))
I ...
- 1
0
votes
1
answer
692
views
Getting "Exceeded maximum execution time (line 0)" error with custom code when data input increased in "History" sheet
I have a google sheet wherein there are 2 sheets named "Position" and "History" respectively.
I have a custom function that takes data in the "History" sheet and arrange ...
0
votes
2
answers
55
views
Custom Function to Cross-Joining
I am looking for a flexible custom function to be able to cross-join (cartesian product) 2 columns of varying lengths in Google Sheets.
My research so far has led me to ask because:
using a ...
- 21
0
votes
0
answers
65
views
Multi-row dependent drop-down list stops working correctly when a row is inserted
Multi-row dependent drop-down list stops working correctly when a row is inserted. I think I need a script so that the drop-down options in column B, that change depending on what drop-down option is ...
- 59
0
votes
1
answer
134
views
Google script (custom function) doesn't get updated inside a cell
I have this function :
function clastrow(targetsheet) {
var SS= SpreadsheetApp.getActiveSpreadsheet();
var Sheet = SS.getSheetByName(targetsheet);
return(Sheet.getLastRow());
}
function ...
- 195
0
votes
1
answer
202
views
openById / openByUrl always returns empty object/null [duplicate]
I am unable to access my spreadsheet via openById or openByUrl. Is there a permission setting I am not aware of? There is no verbose output indicating why it is unable to open the spreadsheet.
0
votes
1
answer
141
views
Custom Script Formula Working with Time Inputs Keeps Returning 12/30/1899, How Do I Stop This
I've looked around and have seen a lot of people ask this same question, but all of the answers I get are either confusing or don't seem to fit with my situation. Here's a dummy sheet (first time, ...
1
vote
1
answer
20
views
Cell formula which returns the number of sheets
I'm trying to add some "validation" to a google sheet to prevent errors from creeping in as the sheet is edited (think assertion when programming).
Because of the way my sheet is structured ...
- 133
1
vote
1
answer
620
views
3D Formula Google Sheets - sum across sheets
I'm trying to script an equivalent to Excel 3D formula into Google sheets.
I have a workbook with numerous worksheets and a master sheet that should sum up the data.
In excel I would have used this 3D ...
- 11
1
vote
0
answers
74
views
Function("url") With A1 Cell Contents as Part of URL Query
I am trying to import JSON data into Google Sheets essentially like IMPORTHTML("URL"&A1&"URL"). Basically trying to adjust the URL by the contents of Cell A1. It seems to ...
- 11
1
vote
2
answers
900
views
In Google Spreadsheet run a function from cell programmatically
I made an Apps Script custom function in a Google spreadsheet.
I can call it from a cell and works fine (In Google Script, get the spreadsheet cell calling a custom function).
The function makes an ...
1
vote
1
answer
73
views
Isolating conditional formatting to each range in a column
I've set up conditional formatting to highlight the top 3 values (gold, silver, bronze) in a range of rows. However, I want to be able to copy/paste the conditional formatting below the initial rows ...
- 13
2
votes
2
answers
1k
views
Convert columns of ISO 8601 strings to dates in Google Sheets
I'm pulling data to my sheet from an API using an ImportJSON function. The data array contains mixed data, including several columns of ISO 8601 date strings in UTC. For example:
2020-06-02T00:00:00Z
...
- 173
1
vote
1
answer
217
views
Google sheets, how do I combine data from two cells, where there is a URL (web-link) in the cells, without loosing the link(url)
I have tried to write a custom function in google sheets.
I tried to create a function to joint the content of two cells. It works for text, however it does not work if the text contains a web-link (...
- 406
1
vote
1
answer
51
views
In Google Sheets,how to get a value lower than a particular cell value in a row?
In Google Sheets, I have numbers in 12 cells in a row. Then in the 13th cell of the same row, I have a number which is one of these 12 numbers. Now, I want find the 1st value lower than the value in ...
1
vote
1
answer
68
views
Is there a way to do a "filter", "if" function across multiple columns to only pull information based on certain criteria and headers?
I do not know how to explain this perfectly without a picture so please take a look at the image before reading.
I am trying to filter the data in Fig 1 (Above). based on the headers and then if the ...
- 25
1
vote
0
answers
77
views
In Google Apps Script, how do you get the Range of the calling cell in a custom function?
I'm trying to get a reference to the calling cell's range in a Google Sheets custom function.
For example:
function MyAddress() {
callingRange = // get calling cell reference here
return ...
1
vote
1
answer
55
views
Friendly Team vs. Enemy attack math and complicated functions in Google Sheets
How would I go about writing a function in Google Sheets?
If Friendly Team Hero Power P2,P4,P6,p8 etc. is greater then Enemy Team Power "T2:T15" Else Don't match it and instead look for titan, Same ...
- 11
1
vote
1
answer
512
views
Problem with COUNTIF in Google Sheets Data Validation Custom Formula
I am following some tutorials and discussion on forcing uniqueness in Google Sheet Column (I need it for ID indexing). I have been using Data Validation for some time, but i have a problem with custom ...
- 11
2
votes
1
answer
54
views
Filter function with multiple variables google sheets
I want to be able to use a filter function for people in NAME whose P DATE is within a specified range. However, if they have a Y under VALIDATED, I want the filter to also include names with the T ...
- 21
0
votes
1
answer
2k
views
"You do not have permission to call ..." Steps to convert a custom function it into an add-on that will have permission?
I'm attempting to create a HIDE() function, which will hide either the current row, column or both based on another (true/false) value.
I've already created the full function, but unfortunately it's ...
- 147
1
vote
0
answers
474
views
Custom function on Google Sheets that can extract hyperlinks from formulated cells
I recently found this post, which is so close to my solution, but not quite there!
Extract the link text and URL from a hyperlinked cell
The issue with this custom formula (=LinkURL()) is that it ...
0
votes
1
answer
37
views
Creating a custom formula in Google Sheets without scripting, but instead based upon another sheet
Ive made a custom calculator in a google sheet, the calculator accepts 5 inputs (arguments) on row 1, rows 2-50 is where is runs the calculation, and the result comes out in row 51.
This calculator ...
- 6,173
1
vote
1
answer
803
views
How to pass an entire sheet as a parameter to a Google Sheets function with dependencies
I have a Google Sheets app script that is passed the name of a sheet. Internally it pulls the data from that sheet and returns a value. Something like this:
function myFunction1(partNumber) {
var ...
- 111
1
vote
1
answer
2k
views
Google Sheets - How to count the occurrence of each day of the week if they are not public holidays?
I would like to count the occurrence of each day of the week in a month if they are not national holidays.
In more details, I would like to know how many Mondays, Tuesdays, Wednesdays, Thursdays, and ...
- 121
1
vote
3
answers
2k
views
Using a script to only take only take cells with values in google sheets
tl;dr how can I make the script take an entire column (eg A:A) and then ignore the first (header) row, and any blank rows?
Background
I have an array in Google Sheets with date-ordered data (note ...
- 11
1
vote
1
answer
191
views
ArrayFormula stops working after shuffling rows
I have a pretty extensive sheet wherein many columns I use an array formula to automatically fill data in from various other spreadsheets.
The problem I have is when I need to rearrange rows then what ...
- 171
1
vote
1
answer
386
views
How to do permutation of characters and print it horizontally along that row in Google Sheets?
I am stuck here in printing permutation of two or more characters horizontally along that row in which the characters are.
Is it possible? Because I searched it all over and only found vertical ...
- 113
1
vote
1
answer
1k
views
Scitpt to duplicate template sheet into new workbook, then rename
I am looking for a script that will create a menu item that will allow me to copy a template sheet that I have created (multiple times) into a new workbook, and rename the copies at the same time.
...
- 11
1
vote
1
answer
79
views
Convert a text of a specific cell or exclude one cell from all text convertion to UPPERCASE
How to adapt this function to exclude a specific cell or how to apply just to one cell?
function onEdit(e) {
if (typeof e.value != 'object') {
e.range.setValue(e.value.toUpperCase());
}
}
0
votes
2
answers
1k
views
Custom SHEETNAME function not working in Arrayformula
I have been successfully deploying the sheetname function outlined in Display sheet name in Google Spreadsheets for a couple of years. It has allowed me to have several sheets collecting information ...
- 3
-1
votes
2
answers
75
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(...
- 1
2
votes
1
answer
261
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
1
answer
77
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 ...
- 27
1
vote
1
answer
2k
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 ...
- 11
3
votes
2
answers
6k
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?
- 5,128
3
votes
1
answer
6k
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 ...
- 479
1
vote
1
answer
293
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
2
answers
837
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(...
- 33
7
votes
6
answers
32k
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....
- 187
-1
votes
2
answers
100
views
Empty cell reference when "Loading..."
I want to do a calculation with imported data from an HTML link. The data is imported every minute. There is loading time and this ensures that the cells are empty during loading. Because my formula ...
- 11