Questions tagged [google-sheets-custom-function]
Use this tag for questions about custom functions in Google Sheets created by using Google Apps Script.
58
questions
1
vote
0answers
23 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
0answers
40 views
Change interpolation javascript from vertical to horizontal
I would like to make this script work horizontally.
This code is from another answer here, but I have not been able to make it work horizontally. I need to use it across columns in one row, ...
0
votes
1answer
14 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
19 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
42 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 ...
0
votes
0answers
12 views
How to make one cell automatically update a timestamp every time another referenced cell is updated? [duplicate]
I want to make a cell in my spreadsheet that updates a timestamp every time another cell is updated.
I have a SUMIF connected to a budget inflows/outflows tab in my spreadsheet and I want this cell ...
0
votes
1answer
49 views
Cut and paste data from a row of cells from one tab to another tab in Google Sheets and repeat with new data
The below code works for Q3 but not for Q5. I'm relatively new to sheets and writing code and I'm sure that you can't have two functions with the same name but am unsure how to resolve so that I can ...
1
vote
3answers
97 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
50 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
40 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
119 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
35 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
325 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
44 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
14 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
49 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
314 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
959 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
1k 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
49 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
317 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(...
0
votes
3answers
3k 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
31 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
3k 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
1k 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
166 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, ...
1
vote
2answers
2k 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
73 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
1k 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
55 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
178 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
24 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
2k 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
1k 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
120 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
63 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
12k 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
389 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
1k 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
214 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
316 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 ...
3
votes
1answer
9k 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
660 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
2k 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
3answers
2k 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
1answer
670 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 |
|--------|:--------:|--------:|-------:|-----:|...
6
votes
1answer
9k 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
1k 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
824 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 ...
