New answers tagged google-sheets
2
You have the basic concept right just piece it together step by step. This does the job:
=REGEXREPLACE(JOIN(" ",ARRAYFORMULA(IFERROR(
REGEXEXTRACT(SPLIT(A1," ",TRUE,TRUE),
"https://example.com/(\S+)"),""))),"\s+"," ")
You split the string at whitespaces, this is safe because URLs don't contain white space so you're definitely not breaking them up. This ...
0
Use REGEXMATCH :
=countif(ArrayFormula(regexmatch(A1:A10,"^Bud Light$")),true)
This formula counts the cells who match the regex ^Bud Light$ : Match exactly Bud Light
You can add lower() and proper()to clean the range you want to test.
0
Use a combination of OR and AND functions
=IF(OR(AND(R2<0.85, OR(I2="T1", I2="T2", I2="T3")), AND(R2<0.95, I2="Broker Audit")), "FAIL", "PASS")
1
I added the custom function:
function getRangeAddress(namedRange) {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var range = sheet.getRange(namedRange);
return range.getA1Notation();
}
and to use it, I did
=getRangeAddress(WidgetTypes)
and it returns C5:M5 as a string.
1
It was a syntax issue, my data range was incorrect.
0
The following script is heavily adapted from an Audit Spreadsheet built by Ben Collins.
The logic followed is:
get the spreadsheet by url openByUrl(); though the script could just as easily have been bound to the 'IMPORTRANGE' spreadsheet
get all the sheets getSheets()and loop through each them
get the last row getLastRow(), and last column getLastColumn()...
0
Use SUBTOTAL
=SUBTOTAL(9, B2:B20)
0
The IF statement in your onEdit(e) script already tests for row and column. You want to test for sheet name also.
This isn't an uncommon question and yours is close duplicate of Google Script onedit(e) event restricted to a specific sheet and specific range with a good answer by @Tanaike.
In your case, extend your if statement like this:
if(e.range....
1
There isn't a built-in function that returns the address of a named range. One alternative is to use a custom function.
The custom function should parse the cell formula to get the name of the named range then the code code could use that string to get the related range and after that you could use getA1Notation.
Related
How to pass a range into a custom ...
0
In order to return the range $C$5:$M$5 you can use the following formula:
=ADDRESS(5,3)&":"&ADDRESS(5,13)
Make a note that when using the function ADRRESS we refer to cell eg.C5 but we first write the row number 5 and then the column number3.
You cannot use the function CELL to get the range of your Named range WidgetTypes.
Why do I get ...
3
With a regex, you can replace each character with itself + ','.
Then use a regular split function.
=split(REGEXREPLACE(A1,"(.)","$0,"),",",true,true)
0
Here's a way to do it with the 26 letters of the alphabet :
=ArrayFormula(
"smith_john_"
&lower(regexextract(ADDRESS(1,ROW(A1:A26),4),"[A-Z]"))&
"@company.com"
)
Result :
0
You want to copy a formula from one sheet to many other sheets in the same spreadsheet.
You are right that a script including getFormula (for which there is relatively little discussion) and setFormula (for which, as @Rubén says, there are many precedents) is on the right track. The following code provides a working example of how you might proceed.
Cell ...
1
You are trying to associate a "text value " in a data validation cell with a given numerical value. One supposes that this is so that you can use the numerical value in another formula. It is common practice in forms for each select item to have a description AND a separate "value", unfortunately Google Sheets does not offer this.
The cell that you use for ...
1
It sounds to me like you're trying to use a spreadsheet to do the job of a database. (2000 rows is nothing. But three concurrent users, 13 tabs, feeding three other worksheets .. put those together and you're not in spreadsheet-land any more).
I'd suggest a review of your overall IT and business architecture, and make decisions from there about what ...
0
Try this formula in cell B2:
=ArrayFormula( IF(A2:A15="Start",row(A2:A15)-1,""))
We make use of the function ROW. ROW returns the row number of a specified cell..
Please read more about ROW here
0
Google Sheets doesn't include a way to assign names to expressions (some people call them formulas) such INDEX(IMPORTXML(MyUrl, MyXPath),1,4)
You could use a named range, but that implies to add the expression in someplace on your spreadsheet and display the result, but you could hide that sheet.
Another alternative is to create a custom function but as ...
1
First formula :
Don't use =!A1:A or =!A1 these are not ranges or cell addresses. The operator ! is used to reference a range in a specific tab like this : =Sheet1!A1
Using =A:A the sumif works.
In E3 : =sumif(A:A,">=2/1/2018",B:B)
Second formula error : Argument must be a range. It's because filtering B:B breaks the formula in the third argument ...
2
If I remember my highschool days, your expression reads :
Sum of the n from 1 to 30.
I.E. 1000*1.07^1 + 1000*1.07^2 + 1000*1.07^3 + ...
Here's a way to do it :
=sum(ArrayFormula(1000*power(1.07,(ROW(A1:A30)))))
Result is 101 073.04
0
This can be done without hardcoding the columns in the formula. I am going to show you how to make a formula that can work with any number of columns without any change to the formula.
We are going to have the filter checkboxes in the first row. I filled the whole first row with checkboxes.
Note that in Google Sheets we can refer to the whole first row as ...
0
You can create a custom function to use Array.prototype.indexOf as it's shown in the question. Next is a simple implementation:
/**
*
* @param {Array} values
* @param {String|Integer} searchElement
* @customfunction
*/
function indexOf(values,searchElement){
return values.indexOf(searchElement);
}
References
https://developers.google.com/apps-...
-1
There is a PowerTools add-on in Sheets that you can use. By selecting the Text tools, you can choose to add a space before of after the content of a cell. Works pretty good for me.
1
Since you are essentially using an ENUM in Google Sheets and the index is increasing by exactly one in each case, your best bet is to use arrays.
If you had a more complicated mapping between integers to strings, you should use the SWITCH function.
Here are examples in both directions (Int to String, and String to Int)...
Option 1: via arrays (preferred)
...
0
Add a new column then try formula this :
=REGEXEXTRACT(A1,"(.*)\/")/REGEXEXTRACT(A1,"\/(.*)")
Or with array formula :
=arrayformula(REGEXEXTRACT(A1:a,"(.*)\/")/REGEXEXTRACT(A1:a,"\/(.*)"))
Then sort/filter the data with this new column.
To revert the process you can try this quick formula to force the custom format :
=TEXT(B1,"0/?????")
...
0
I have done something similar using IFTTT.
create a IFTTT web-hook trigger to create your custom URL
the IFTTT action linked to that trigger is a 'add row to google sheets spreadsheet', which is off-the-shelf
The web-hook part documentation can be found here and will probably need some tinkering.
0
Use Regexmatch instead of Istext :
=REGEXMATCH(indirect("Sheet2!B1"),"specific string to check")
The second argument of regexmatch is a regular expression so you can use operator like |,.,/ or escape them with \. Also be sure to pay attention at lower/upper case issues because it's case-sensitive.
1
As this post is old but the current data has changed I will add a new answer for (2019) data.
Again for your cell count the answer is still: NO
That said google has upped its cell limit to 5 million. I have tested this and it will load exactly 5 million cells into google sheets but no more.
As a reference at the time of this post the current limitations ...
1
Redirectnator v1.1 is a spreadsheet created by Jeff Louella. It has a function built-in, with server code (301/302/200/404) and final url. You can make a copy here : https://www.thetechseo.com/seo-tools/redirect-checker/
Here's the script you can use/modify :
function redirectCheck(url, user, pwd) {
try {
function getResp(url, user, pwd){
...
0
Google has recently added a Goal Seek add-on. See the Google Help Article "Find an unknown value with Goal Seek" at https://support.google.com/docs/answer/9506732
or do a search on "Google Sheets Goal Seek" for more information.
0
You can use something along these lines:
=((countif($b18,Week1!$b$10:$b) + countif($b18,Week2!$b$10:$b) + ... ) = 12)
Good luck!
2
To move a range of cells to a new location in Google Sheets:
1. Select the range of cells you want to move
2. Move your mouse to any edge of the selection until the cursor changes into a hand
3. Drag your cells to the new location
This does what it should - it moves them - so all cell information will remain exactly as they were in their original ...
1
(In your example spreadsheet you have no formulas)
Most likely your formula calculated results are given in TEXT format, so AVERAGE -which needs numbers to make calculations- can NOT give any results and you end up with #DIV/0!.
A quick and easy fix:
To change text cells to number cells, select your range (C2:C) and choose a number format for it by ...
0
What you are looking for is to lock the formula.
The dollar sign $ is used for "locking" a cell.
Form Responses 1'!$B$3 The cell is fully locked.
Form Responses 1'!$B3 The column of the cell is locked.
Form Responses 1'!B$3 The row of the cell is locked.
Form Responses 1'!B3 The cell is unlocked.
EDIT: (following OP comment)
What I need is =('...
1
Yes, there is a much simpler way to do what are you trying to do:
=INT(AC11/10)
Good luck!
0
Why do you use SUM on a single cell. SUM needs more than one cells to give you any results. Please read more about SUM
Also, you have to correct your formula to:
=IF(AND(O11>0, O11<25), "0",
IF(AND(O11>26, O11<50), "1",
IF(AND(O11>51, O11<75), "2",
IF(AND(O11>76, O11<100), "3",
IF(AND(O11>101, O11<125), "4", "??...
1
You can try something along these lines (assuming your inputs are on cell O11)
=INT(O11/25)
-1
The answer above isn't quite correct. Users with access to sheet A can sheet B if sheet A have been given permissions to use IMPORTRANGE. All they have to do is create an import range covering all the the data in sheet B.
0
This feature is not currently built into Google Sheets, which is a ridicules as it's integral for productive workflow.
I have found a much easier work around then what was provided in this thread so far.
I saw there was a table styles add-on you could include, but it did not install for me.
0
I think it's usefull for an admin the option of disabling this feature at cell level or at spreadsheet level
And it is also needed a way of removing cell history whenever an admin decides that. All the information in a sheet is supossed to be owned by his owner/admin and Google can't impose the existence of this información when admin don't want to have it ...
0
The penny has dropped, and the answer to your question is "Yes".
Though a form may have many records for a given trailer, that trailer can only be on one job at a time though its status may change (which I suppose is what the database records). Presumably it is the latest form response for the trailer that is important; and if not, then an alternative ...
0
If you go with the asterisks option and want a cell reference, you can use the CONCATENATE function to add the asterisks
=COUNTIF(A1:A100,CONCATENATE("*",A1,"*"))
2
Since you have already tried the query solution, please use these two formulas:
in cell B2:
=query(D6:E10, "select count(D) where D='paid' and E='MB' label count(D) ''")
in cell D2:
=query(D6:E10, "select count(D) where D='paid' and E='UB' label count(D) ''")
You can read more about how the QUERY function works in the help center.
Note: In the ...
0
I recommend using:
=IF(COUNT(E4:G4);AVERAGE(E4:G4);0)
COUNT() returns numbers of cells containing valid numbers : if none, the value will be 0, so the condition will evaluate to false and return 0 (you can put "" instead of 0 if you prefer). Otherwise, it will use the result of the AVERAGE function.
0
If you, as Ryan Shillington suggests, want to know the number of cells within a range that has "sites" in it, you might try:
=COUNTIF(A1:A100;"*sites*")
The * acts as a wildcard notion, so it will look for a match anywhere within the cell, not just an exact match for the whole cell.
I've also done this with a cell reference instead of "sites", but ...
1
Data type : strings
use some concatenation. Try this in D1
=ArrayFormula(A:A&B:B&C:C)
Data type : value
If you have number data then include a VALUE() function like so:
=ARRAYFORMULA(VALUE(A:A & B:B & C:C))
Credit to @iandllnghm in the comments of this answer.
1
We can use the OFFSET function in the following formulas:
+----+----+---+---+-----------------+
| A | A | B | C | D |
+----+----+---+---+-----------------+
| 1 | a | | | =A1 |
| 2 | | b | | =OFFSET($A2,,1) |
| 3 | | | c | =A3 |
| 4 | | d | | =OFFSET($A4,,2) |
| 5 | e | | | =OFFSET($A5,,...
1
There is a build-in function for that in Google Sheets.
=today()
Just write that in a cell of your choice. You only then need to format the cell and display the date as you like by going to Menu--> Format --> Number --> More Formats --> More date and time formats.
You can choose and also create your own variations.
0
You can achieve this using the filter function :
=filter(A:Z,arrayformula(not(isblank(B:B)))
A:Z is the range you want to filter
B:B is the column containing any empty cells (your condition).
Same result with a filter view :
With a query :
=query(IMPORTRANGE("https://docs.google.com/spreadsheets/d/sheetid","Sheet1!A1:250"),"SELECT * where Col2 <&...
1
Iframes are not allowed in GitHub
As stated on the GitHub site here and here, iframes are not allowed in GitHub
There may be a workaround though through Zap (may be because I haven't tried it myself).
Once you create your Zap account you get to see quite a few triggers.
Please give it a try and let us know how it goes.
0
What exactly do you mean by "a live preview image"?
On most sites you can usually have either an iframe or an embedded image or a link to it. But it all depends on the site and not the source of the image (unless hot linking is disabled).
In any case you can get the shareable link like this of a google file by:
- Right-clicking on the file
- or ...
Top 50 recent answers are included
