Questions tagged [google-sheets-custom-function]

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

25 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
2
votes
1answer
31 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 ...
1
vote
0answers
25 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 ...
1
vote
0answers
47 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
379 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
81 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 =...
1
vote
2answers
5k 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 ...
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
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
1answer
20 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
18 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 ...
0
votes
0answers
11 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
21 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
33 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 ...
0
votes
0answers
23 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 ...
0
votes
0answers
13 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 ...
0
votes
0answers
42 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
23 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 ...
0
votes
1answer
154 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
0answers
404 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
0answers
80 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
1answer
400 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(...
0
votes
0answers
221 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
978 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
votes
2answers
35 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
votes
2answers
48 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(...