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
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: ...
0
votes
1answer
2k 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
3answers
5k 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
838 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 | |--------|:--------:|--------:|-------:|-----:|...
0
votes
2answers
11k 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
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
22 views

Create a Function in Google Sheets that shows the User's information [duplicate]

I would like to add a Function to my Google Sheets, so I can show in a cell the user that changed another cell (only the latest change), with the choice of showing the date and time of the change also....
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
34 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
0answers
17 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
155 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
413 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
445 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
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
982 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(...

1
2