Questions tagged [google-sheets-custom-function]
Use this tag for questions about custom functions in Google Sheets created by using Google Apps Script.
72
questions
-1
votes
0answers
11 views
Does running a custom function get triggered every 30 minutes with importrange? [duplicate]
I have a Google spreadsheet that I used an importrange function to get addresses from another sheet. Then I added this into the script editor to convert those addresses to coordinates using a custom ...
-1
votes
0answers
15 views
Google sheets custom function runs when it shouldnt [duplicate]
I have a google spreadsheet that I used an importrange function to get addresses from another sheet. Then I added this into the script editor to convert those addresses to coordinates using a custom ...
0
votes
0answers
59 views
#Error! in ImportJSON formula in google sheets
I have been successfully using the following ImportJSON formula in google sheets:
=ImportJSON(csgoempire.com/api/v2/metadata/roulette/history?seed=1997, "/", "noInherit,noTruncate,...
1
vote
1answer
40 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 ...
2
votes
2answers
235 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
...
0
votes
1answer
37 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 (...
0
votes
0answers
19 views
Decrease months back when typing numbers in Google sheet
I'm trying to make a Google sheet for my savings account where my goal is to reach X money before X date and I have several fields for each month and a field to every month where I have the amount of ...
1
vote
1answer
26 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 ...
0
votes
0answers
12 views
Is there a way to import data from an added cell into a single cell?
I'm in charge of keeping track of Teacher/Student communication. Each student has their own data sheet where the teachers can update with new dates as they talk to students. Is there a way to pull the ...
0
votes
0answers
23 views
How do I return all of the email addresses from column D in one Google Sheet that are not present in column D of a different Google Sheet
I want to build a function that will return all of the email addresses from column D in one Google Spreadsheet that are not present in column D of a different Google Spreadsheet.
This could be in ...
0
votes
0answers
77 views
How to fetch only the required data set for a query in ImportJSON?
I am trying to fetch the latest price of BTC/INR from a JSON HTTP URL of a exchange.
https://api.wazirx.com/api/v2/trades?market=btcinr
I am using the following formula in Google Sheet to Fetch the ...
1
vote
1answer
46 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 ...
1
vote
0answers
32 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
1answer
50 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 ...
1
vote
1answer
240 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 ...
2
votes
1answer
35 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 ...
0
votes
1answer
497 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 ...
1
vote
0answers
130 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
1answer
26 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 ...
1
vote
1answer
204 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 ...
1
vote
1answer
543 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 ...
1
vote
3answers
969 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 ...
1
vote
1answer
94 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 ...
1
vote
1answer
228 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 ...
1
vote
1answer
552 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.
...
1
vote
1answer
67 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
2answers
910 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 ...
-1
votes
2answers
57 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
41 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
69 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 ...
1
vote
1answer
992 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 ...
1
vote
2answers
4k 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?
1
vote
1answer
3k 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
117 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
570 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(...
4
votes
4answers
18k 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....
-1
votes
2answers
41 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 ...
1
vote
2answers
6k views
Cell values do not update
I want to update every minute a cell with data from a HTML link. The problem is that the value does not update unless I manually empty the cell or make changes to the script. I tried to add NOW() to ...
0
votes
1answer
2k 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
384 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, ...
2
votes
3answers
4k 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
206 views
How to update this formula so it automatically updates?
It's come to my attention that GoogleClock no longer works on new sheets, anyway I can update the below code so it can update every minute:
Tried put =SheetNames(NOW())
It says not allowed. I'm very ...
0
votes
1answer
3k 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
1
vote
0answers
26 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
3k 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
2k 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
560 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(...
2
votes
2answers
24k 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 = Utilities....
0
votes
1answer
406 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
1answer
2k 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 ...
