Questions tagged [google-sheets-custom-function]

Use this tag for questions about custom functions in Google Sheets created by using Google Apps Script.

Filter by
Sorted by
Tagged with
0
votes
1answer
25 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
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 ...
0
votes
2answers
12k views

How do I modify this Google formula to filter out blank responses from Google Sheets?

I have Google Sheets running the add-on Form Mule to automatically send emails with the responses to a Google Form. Using the following formula, I was able to get a nice HTML table to show up in the ...
0
votes
1answer
192 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 ...
1
vote
1answer
491 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
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 ...
2
votes
1answer
34 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 ...
12
votes
2answers
55k views

Calculating shaded cells

I am using Google Spreadsheets to create a matrix (time/activity) as a daily schedule for staff to sign into. Is there a formula to calculate the number of cells in a column that are shaded (or not ...
1
vote
1answer
105 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 =...
0
votes
1answer
401 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(...
3
votes
2answers
15k 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"...
1
vote
1answer
39 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 ...
19
votes
2answers
43k views

How to import JSON data into Google Spreadsheets?

There is IMPORTXML function to import data from any of various structured data types including XML, HTML, CSV, TSV, and RSS and ATOM XML feeds. Is there any similar solution to import data in JSON ...
0
votes
1answer
28 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 (...
2
votes
2answers
147 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 ...
1
vote
1answer
24 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
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 ...
55
votes
12answers
68k 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: ...
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 ...
4
votes
4answers
16k 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
47 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
45 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
2answers
4k views
0
votes
0answers
27 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 ...
2
votes
1answer
1k views

substitute() multiple terms in Google Spreadsheets

I'm trying to simplify semi-complex text strings into much simpler ones - specifically names into hashtags. For example: Tomáš Rosický into #TomasRosicky or Alex Oxlade-Chamberlain into #...
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 ...
5
votes
1answer
2k 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 ...
0
votes
0answers
19 views

Randomly distribute the sum of a number between cells in Google Sheets

I'm planning out a Kickstarter campaign and I'm making a sample tier list in google sheets to see if I can get the funding needed with minimal backing. I've randomly added in numbers to the estimated ...
1
vote
1answer
196 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
2k 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); ...
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
348 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
102 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 ...
1
vote
1answer
146 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
votes
2answers
52 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(...
0
votes
3answers
6k 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 ...
1
vote
1answer
424 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
votes
2answers
36 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
3answers
796 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 ...
3
votes
1answer
7k views

How can I solve error “Exceeded maximum execution time” in Google Sheets?

I found a script which I really need (link): function getTotalSum(cell) { var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets(); var sum = 0; for (var i = 0; i < sheets.length ...
3
votes
1answer
2k views

How can I reference a cell in a custom function while setting up data validation in Google spreadsheets?

I made the following Google script to check whether a string is less than some length: function isLengthLessThan(length, text) { return (text.length < length) }; How can I set up data ...
2
votes
1answer
224 views

Google Sheets Passing Range from separate sheet into custom function

I have the following custom function I've built to basically reproduce a needle haystack type of functionality function ListBy (Range, Range2, needle) { var holder = []; if(typeof Range != '...
2
votes
2answers
22k 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....
2
votes
3answers
441 views

Code invoked too many times

My goal is pretty simple: take the value in column U and check if it's within particular ranges. If it's 1–100, put a 1 in column A. If it's 101–200, put a 2 in column A. The problem is, I have almost ...
2
votes
1answer
1k views

Prevent “invoked too many times per second” error in Google Apps Script

I have a Google spreadsheet consisting of different months on each tab. Every month, I add a new tab and another 50-75 rows. There are many tabs. There is a cell on each row which calls a custom ...
1
vote
1answer
90 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 ...
0
votes
1answer
1k 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
202 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
917 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 ...