Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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: ...