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
1 vote
0 answers
18 views

How to parse multiple cells in one go using ImportJSON()?

I have a dataset with 14 columns. Two of the columns contain text that is in JSON format. I am currently parsing the data with ImportJSON(), a script that is a collection of custom functions. It has ...
Boris Fredoc's user avatar
0 votes
1 answer
33 views

Does Google Sheets have a function to insert X number of Y characters within a cell? [duplicate]

I currently have a spreadsheet which requires a variable number of line breaks within a cell based on input data from another cell. Long story short, I currently am using a brute force method to ...
G0vnah's user avatar
  • 3
0 votes
0 answers
21 views

Putting a custom function in same file as Apps Scripts [duplicate]

I'm sure I'm missing something very simple, but here is my problem. I have a spreadsheet with an apps script that has global variables that require authorization (due to openBy). I want to add a ...
E.B. Sandberg's user avatar
0 votes
1 answer
20 views

Google Sheets: Creating CUSTOM RULE in conditional formatting to search column contents and highlight row

I'm looking to find one word inside cells in column E with multiple words, and when it's found format that whole row. I am so lost... This is as close as I got =$E201=REGEXMATCH(LOWER(E:E), "...
Ryan Owens's user avatar
0 votes
1 answer
557 views

All columns values in one column

I need some help! :S I have a spreadsheet and three list of items (Column "A", "B" and "C") and I need to put all the values of all the columns in only one column (Column ...
Axel Frey Guarenas's user avatar
1 vote
1 answer
194 views

How to return a Lambda Function through a Custom Function?

I know there are alternatives to this. You can simply pass the variables you want to pass to the custom function. But that's not what I want to happen. Something like: =CUSTOMFUNCTION(A2,A3)(A4,A5) ...
David Tan's user avatar
  • 967
-1 votes
1 answer
15 views

Subtracting Amounts from Specific Categories

I'm working on trying to make a relatively simple spending tracker using Google Sheets. In this example, the person has $5000 already in the bank, and they have earned $1000. The categories of Housing,...
Hayden's user avatar
  • 1
3 votes
2 answers
244 views

How to select range into a cell based on drop down selection

I have two sheets in my Google spreadsheet: Meal Plan and Recipes. Meal Plan looks like this: Recipes looks like this: I would like to write a formula or function which allows me to select a meal ...
Matt W's user avatar
  • 195
0 votes
0 answers
68 views

Auto delete certain rows on the first of every month - Google Sheets

I've built a Sheet on Google Sheets with multiple tabs and formulas for me and my coworkers to use. Only thing I couldn't do is set a formula that deletes multiple specific custom cells on multiple ...
Tal's user avatar
  • 1
0 votes
1 answer
138 views

Conditional Formatting Formula based on multiple criteria

I am needing help building conditional format formula for Google that meets the following criteria: Column B email needs to appear more than 10 times AND Column I data needs to be "healthy" ...
Louisa's user avatar
  • 3
2 votes
2 answers
2k views

Google Sheets: Remove column headers from a QUERY()

I'm trying to eliminate (not hide) the column headings from the following QUERY(). The output from the =QUERY() must be in a single row. =QUERY(myData!A:G, "Select SUM(E), SUM(F) WHERE D ='"&...
Mr. B's user avatar
  • 2,857
0 votes
1 answer
42 views

Google Sheets I need the column sums() of a table produced by a query()

I have a query() that outputs a table with two columns (A and B) of numbers. Pictured in IMAGE #1. =QUERY(myData!A:G, "Select E,F WHERE D ='Books'") IMAGE #1 But I need a function that ...
Mr. B's user avatar
  • 2,857
0 votes
1 answer
19 views

Counting different types of text and producing specific text outcome

So I'm trying to create a tracker where I can gauge a pass/warning/fail status for each category. Under "Report Status", I wanted a function that can put out the text "Fail" if one ...
Sheet my Pants's user avatar
0 votes
1 answer
327 views

Trying to change Gantt chart in Google Sheets to highlight a cell based on date range and progress percentage

I have been working on a dynamic Gantt chart template in Google Sheets for awhile now. Here's how it currently looks: As you can see, the task bar currently highlights gray dependent on the start and ...
Stefanie's user avatar
0 votes
2 answers
1k views

Google Sheets sum-by-color function is no longer working. Possible to update?

Been using the following function to generate the sum of same-colored cells: function sumColoredCells(sumRange,colorRef,test) { var activeRg = SpreadsheetApp.getActiveRange(); var activeSht = ...
Nick's user avatar
  • 17
2 votes
1 answer
1k views

How to pass multiple comma separated values in a cell to a custom function?

Suppose, there are values such as 1,A, 2,B, 3,D in certain cells. My intention is to add 1,2,3 (LHS) separately and A,B,D (RHS) separately. To achieve that, I have written certain custom function in &...
iammilind's user avatar
  • 173
0 votes
0 answers
18 views

Reference same cell from multiple worksheets automatically with list going down? [duplicate]

I was wondering how can I make my main /master sheet more automatic so I don't have to input every data such as =sheet1!b2, =sheet2!b2, etc. I was thinking if there is some way to make it more ...
Bn Minki's user avatar
-1 votes
1 answer
102 views

QueryByName() function from another sheet in google spreadsheet

Is it possible to use the QueryByName() function using another sheet from the same spreadsheet? =query(nor9609s!A1:Z500,querybyname(A1:Z1,"SELECT `Items_shopid` WHERE `Items_id`>0")) I ...
lochoe's user avatar
  • 1
0 votes
2 answers
4k views

Getting "Exceeded maximum execution time (line 0)" error with custom code when data input increased in "History" sheet

I have a google sheet wherein there are 2 sheets named "Position" and "History" respectively. I have a custom function that takes data in the "History" sheet and arrange ...
Neeraj Mandloi's user avatar
0 votes
2 answers
174 views

Custom Function to Cross-Joining

I am looking for a flexible custom function to be able to cross-join (cartesian product) 2 columns of varying lengths in Google Sheets. My research so far has led me to ask because: using a ...
James Gregg's user avatar
0 votes
0 answers
192 views

Multi-row dependent drop-down list stops working correctly when a row is inserted

Multi-row dependent drop-down list stops working correctly when a row is inserted. I think I need a script so that the drop-down options in column B, that change depending on what drop-down option is ...
user246475's user avatar
0 votes
1 answer
911 views

Google script (custom function) doesn't get updated inside a cell

I have this function : function clastrow(targetsheet) { var SS= SpreadsheetApp.getActiveSpreadsheet(); var Sheet = SS.getSheetByName(targetsheet); return(Sheet.getLastRow()); } function ...
andio's user avatar
  • 315
0 votes
1 answer
472 views

openById / openByUrl always returns empty object/null [duplicate]

I am unable to access my spreadsheet via openById or openByUrl. Is there a permission setting I am not aware of? There is no verbose output indicating why it is unable to open the spreadsheet.
Aurora Pariseau's user avatar
0 votes
1 answer
280 views

Custom Script Formula Working with Time Inputs Keeps Returning 12/30/1899, How Do I Stop This

I've looked around and have seen a lot of people ask this same question, but all of the answers I get are either confusing or don't seem to fit with my situation. Here's a dummy sheet (first time, ...
Ronnie Walker's user avatar
1 vote
1 answer
27 views

Cell formula which returns the number of sheets

I'm trying to add some "validation" to a google sheet to prevent errors from creeping in as the sheet is edited (think assertion when programming). Because of the way my sheet is structured ...
Att Righ's user avatar
  • 184
1 vote
2 answers
137 views

Google sheets range of sheets? [closed]

I have multiple sheets in my Google Spreadsheet. Let's suppose that they are named "1", "2", ... "n". I would like to sum the contents of the B4 cells in all of these ...
Him's user avatar
  • 123
2 votes
1 answer
1k views

3D Formula Google Sheets - sum across sheets

I'm trying to script an equivalent to Excel 3D formula into Google sheets. I have a workbook with numerous worksheets and a master sheet that should sum up the data. In excel I would have used this 3D ...
Mady's user avatar
  • 21
1 vote
2 answers
2k views

In Google Spreadsheet run a function from cell programmatically

I made an Apps Script custom function in a Google spreadsheet. I can call it from a cell and works fine (In Google Script, get the spreadsheet cell calling a custom function). The function makes an ...
Rodrigo E. Principe's user avatar
1 vote
1 answer
127 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 ...
Olivia's user avatar
  • 13
-1 votes
1 answer
478 views

ARRAYFORMULA and FILTER

The following sheet is an example of what I'm trying to do : https://docs.google.com/spreadsheets/d/1B9a8OUvwciCAYURLZq-Jd2KeSUZsYlalAFNsANzRzNw/edit?usp=sharing In the first sheet ("Matrix"), I have ...
Rowin's user avatar
  • 119
3 votes
2 answers
2k 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 ...
Julius's user avatar
  • 243
1 vote
1 answer
510 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 (...
ctrl-alt-delor's user avatar
1 vote
1 answer
352 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 ...
alerts4 deepak's user avatar
1 vote
1 answer
263 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 ...
Lina's user avatar
  • 25
1 vote
0 answers
149 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 ...
Caleb McNevin's user avatar
1 vote
1 answer
58 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 ...
Abe.S's user avatar
  • 11
1 vote
1 answer
705 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 ...
osek's user avatar
  • 11
3 votes
2 answers
76 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 ...
Kyle's user avatar
  • 31
0 votes
1 answer
3k 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 ...
fsteff's user avatar
  • 147
1 vote
0 answers
534 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 ...
Jamie Johnson's user avatar
0 votes
1 answer
50 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 ...
sam's user avatar
  • 6,843
1 vote
1 answer
2k 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 ...
Jim B.'s user avatar
  • 111
1 vote
1 answer
2k 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 ...
Norbert Biró's user avatar
1 vote
3 answers
3k 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 ...
Tobes's user avatar
  • 11
1 vote
1 answer
282 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 ...
Marek Marhoul's user avatar
1 vote
1 answer
439 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 ...
RageRemix's user avatar
  • 113
1 vote
1 answer
1k 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. ...
No Name's user avatar
  • 11
1 vote
1 answer
86 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()); } }
Leandro Amorim's user avatar
0 votes
2 answers
2k 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 ...
DHolcomb's user avatar
-1 votes
2 answers
108 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(...
Nick's user avatar
  • 1