New answers tagged google-sheets
0
You are not able to check for "connection" anywhere in Google Sheets. Such feature does not exist at the moment. As for the "what access rights" part: a spreadsheet that is connected can be read (scrapped) with IMPORTRANGE function and this can also be exploited to gain direct access (not ownership) to the whole spreadsheet.
1
I used the script below, located in https://stackoverflow.com/q/56215898/7108653, like suggested for @Tanaike, and adjusted with just a few things (lines 3, 4 and 5).
function downloadSheetAsPDF2() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetId = ss.getSheetByName("RECEITA PARA IMPRESSÃO").getSheetId();
var valor = SpreadsheetApp....
0
There is an add-on that does this called LegendSheet. However, you don't have the number and the unit in the same cell rather you have to put them side by side. But it handles all the unit conversions such that you could do something like this:
| A | B | C | D | E |
--+-----+------+-------+---+-----------------------+
1 | 3.0 |...
0
I question that Google is exporting CSVs as UTF-8. I exported a sheet as csv and ran the following bash command:
file -I "file_name.csv"
and it returns...
file_name.csv: text/plain; charset=us-ascii
Not sure if it matters, but used a Chrome browser for this.
0
Use a pivot table, and sort it by sumof column2 instead of the unique + sumif combination that you are using. See this spreadsheet to see it working on your data.
1
Set A1 to be the first day of the month in question. Then:
=CEILING((DAY(EOMONTH(A1, 0)) - MOD(6 - WEEKDAY(A1), 7)) / 7)
This works for any day of the week, just change the 6 after MOD( to the corresponding day of the week.
How it works:
6 - WEEKDAY(A1)
This counts the days between the first day of the month and the nearest Friday. However, we want to ...
0
=ARRAYFORMULA(IF(LEN(A2:A&B2:B&C2:C&D2:D&E2:E),
MMULT(A1:E1*A2:E,TRANSPOSE(COLUMN(A1:E1)^0)), ))
0
Try the below script
In Google Sheets how do I duplicate a sheet along with its permission
It works from the "owner" account ... not from "editor" account. I have tested and it is working
-1
Try running the script from the "owner" not from "editor" account
0
custom formula: =NOT(COUNTBLANK(B2:D2))
0
custom formula applicable to range A10:A998: =B10="later"
0
The rule you need to apply to range A1:A is:
=AND(B1:B<>"", C1:C<>"", D1:D<>"")
0
We finally came up with an eloquent solution using the ARRAY_CONSTRAIN, SORT, QUERY functions.
This is the final formula, small, flexible (both variables out of the formula), row expandable, robust (works with empty cells):
=ARRAY_CONSTRAIN(SORT(
QUERY(A2:E, "select * where D >= date '" & text(J21, "yyyy-mm-dd") & "'"),
4,FALSE,5,),M21,5)...
0
I found the first answer correct but easier to understand when adding the following details. On a desktop, add the google drive app, then log into “google drive” - still on a desktop, open your sheets project ( it will be here even if you had been working on a tablet since “drive” manages all the google docs, sheets, & slides suite of apps ) then ...
0
custom formula for all 3 rules: =(A1=10)*(B1=10)*(C1=10)
0
I solved my zip code problem that begins with a "0" this way...
Highlight column, go to format tab, click on "number", "more formats" and then "custom number formats"
Since my zip codes are only 5 digits I typed in "00000" and hit apply.
and it worked!
0
weeknum is not a valid scalar function so you need to trick it like this:
=ARRAY_CONSTRAIN(ARRAYFORMULA(QUERY({A2:B, WEEKNUM(A2:A)}, "where Col3 = 21")), 999^99, 2)
=FILTER(A2:B, WEEKNUM(A2:A)=21)
0
Convert a text of a specific cell ... to UPPERCASE
try like this which should upper Sheet1!A2 cell:
function onEdit(e) {
var sheet, sheetName, colToCapitalize;
sheet = e.source.getActiveSheet();
colToCapitalize = 1;
sheetName = 'Sheet1';
if (sheet.getName() !== sheetName || e.range.rowStart = 2 || e.range.columnStart !== colToCapitalize ...
1
=ARRAYFORMULA(IF(LEN(A1:A&B1:B&C1:C&D1:D&E1:E),
MMULT(IFERROR(LEN(IFERROR(REGEXEXTRACT(A1:E, "[A-Z]+")))/
LEN(IFERROR(REGEXEXTRACT(A1:E, "[A-Z]+"))), 0),
TRANSPOSE(COLUMN(A1:E1)^0)), ))
3
Simplifying fractions can easily be done with the GCD-function (Greatest Common Divisor).
Using this function
=A1/GCD(A1;B1) & ":" & B1/GCD(A1;B1)
will give you this result:
A B C
1 2 5 2:5
2 5 15 1:3
3 8 18 4:9
If you really want half points, just multiply all values with two and at the end divide them by two again:
=(2*A1/...
0
custom formula applied to whole range: =IF(A$1=TODAY(),1)
0
instead of your formula in B column and the whole pivot table, use:
=ARRAY_CONSTRAIN(ARRAYFORMULA(REGEXREPLACE(QUERY(IF(LEN(A2:A),
{WEEKDAY(A2:A), TEXT(A2:A, "dddd am/pm hh:00 am/pm")}, ),
"select Col2,count(Col2),Col1
where Col2 is not null
group by Col2,Col1
order by Col1
label count(Col2)''", 0)&"AM ", "PM |AM ", "")), 999^99, 2)
0
This is bit better:
=FILTER(A1:A20,A1:A20 > E1)
0
you will need a script like this:
function onEdit(e) {
var aCell = e.source.getActiveCell(), col = aCell.getColumn();
if(col == 5) { //number of column where you have a checkbox
var adjacentCell = aCell.offset(-2,-3);
var newDate = Utilities.formatDate(new Date(),
"GMT+1", "dd/MM/yyyy hh:mm:ss");
adjacentCell.setValue(newDate);
}}
0
all you need to do is to add more series for Y axis:
1
first you will need to pivot out series with QUERY formula and then plot the chart
=QUERY(A1:C11, "select A,C,sum(C) where A is not null group by A,C pivot B", 1)
0
months in QUERY starts from 0 so you need to fix it with +1
=QUERY(Database!A1:AD, "select A,E,F,U,V,W,X,AB,AC where month(A)+1 = 5", 1)
0
The first argument of the HYPERLINK function is not well constructed as it includes a semicolon ; before the question mark ? and an ampersand & after the question mark ? and the ampersand & on cell A7's value is not escaped. Replace it with %26
Reference
Escaping ampersand in URL
mailto
0
if you have a scenario like shown on the image all you need is custom formula =B1=TRUE applied to range A1:A
0
I have a cell with a check box. I want another cell which contains a
date to go green when the tick box is checked.
You go to Format, Conditional Formatting and use the Custom formula:
=COUNTIF($D3, "=TRUE")
EXTRA:
On the other hand, if you want the whole range/column to be highlighted use the following formula:
=COUNTIF($B$3, "=TRUE")
Pay ...
-1
=IFERROR(SUM(H10,N10,T10,Z10,AF10,AL10)/COUNTA(H10,N10,T10,Z10,AF10,AL10))
1
On Firefox used Alt + Up and Alt + Down to move the worksheet and avoid conflicts with moving the browser tab.
0
=TRANSPOSE(SPLIT(JOIN(","; ARRAYFORMULA(REPT(SPLIT(
INDIRECT("B2:B"&COUNTA(B2:B)+1); ",")&",";
INDIRECT("C2:C"&COUNTA(C2:C)+1)))); ","))
0
If E1 contains the word Archery you need to do this:
=QUERY(IMPORTRANGE("ID", E1&"!A4:E3000"), "select Col3,Col2 where Col1 like '%TRUE%'")
1
={A1:E1; QUERY(SORT(VLOOKUP(QUERY({ROW(D:D), SORT(A:E)}, K1, 0),
{ROW(D:D), SORT(A:E)}, {2, 3, 4, 5, 6}, 0), 4, 0),
"where Col4 <="&INDEX(QUERY(SORT(VLOOKUP(QUERY({ROW(D:D), SORT(A:E)}, K1, 0),
{ROW(D:D), SORT(A:E)}, {2, 3, 4, 5, 6}, 0), 4, 0),
"select Col4
order by Col4 desc"), 2, 1)&"
and Col4 >="&INDEX(UNIQUE(QUERY(SORT(...
0
try CTRL + H
select specific range if its only in one cell
and also select within formulas
0
Actually, I solved the issue with the creating hyperlink that downloads selected range with all the parameters. For my purposes download PDF even better. So instead of script-button, I placed a hyperlink to a cell and it downloads PDF perfectly.
0
=AVERAGE(QUERY({INDIRECT(B1&C1&"!A2:I")}, "select Col9 where Col2 = '"&A5&"'"))
0
press CTRL + F
type your word to find the cell
select the cell
click into address bar
press CTRL + F
type the word to find the possition
1
Google Sheets do NOT highlight specific words in text.
Google sheets ONLY show the cells where your search terms are included
After finding the right sheet, use your browser's search feature.
Please pay attention on the counted results.
Google Sheets function ONLY shows found cells (including the search terms)
This is what you see in Google Chrome:
....
0
You want to copy a sheet from the active Spreadsheet as new Spreadsheet.
You want to copy only the values of the sheet.
You want to keep the format of cells.
If my understanding is correct, how about this modification? Please think of this answer as just one of several answers. In this modification, I used the following flow.
Copy the active Spreadsheet.
...
1
You are supposed to use a cell reference of the cell you have a value in so instead =15*12 you do =A$1*12 and drag down:
if you don't want to play with dragging you need to use array formula:
=ARRAYFORMULA(IF(ROW(A1:A10), A$1*12, ))
0
This is standard behaviour and can not be changed. If you want to sort multi-column range at once where sorting is not performed per single column but rather per whole rows in a given range, you are supposed to select a range you wish to sort and choose from here:
0
This is caused because the format of cells is not unified and can be fixed either with:
buttons
or button
Select range B32:E34 and set it as Currency:
0
SOLVED
Ended up using the following formula (after creating an Open/Closed Status column)
=IF(AND(C2="Open",D3""),L2+T$29, IF(AND(C2="Closed",D3""),L2+0,""))
0
Inspired from the accepted answer, here is the version if you want to find in notes in all sheets:
function searchNotes() {
var allSheets = SpreadsheetApp.getActive().getSheets();
var sheet = SpreadsheetApp.getActiveSheet();
var cell = sheet.getActiveCell();
var searchTerm = cell.getValue().toString().toLowerCase();
var results = [];
for(var ...
0
From: "How to Use the Explore Feature in Google Sheets":
Open the Explore panel by hitting the Alt+Shift+X shortcut.
Choose your question carefully. Try "find column status blank", that should find the first occurance of the word "blank" in the column "status".
Click the "See formula" option to get the formula and copy that to your Spreadsheet.
Now you have ...
0
There is a simpler way than the formula on the other answer but it could be used only if your spreadsheet have free cells to use as Google Sheets spreadsheets have a 5 million cells limit and this alternative will potentially double the required cells by the other hand this also could make your spreadsheet slower.
In summary add a new tab and a array to it....
0
On Google Sheets formulas results aren't mutable. One alternative is to replace the formula by it's results. This can be done "manually" with the help of copy > paste as values. Another alternative is to use a macro or script either.
The way to turn update the filtered results is to remove the filter, put back the formula, then reapply the filter.
A third ...
0
Your code is iterating over the rows got by getDataRange / getValues and it's using file.next() on each iteration so it's very likely that that there are fewer files than rows.
Top 50 recent answers are included
