New answers tagged google-sheets
0
Make sure to first delete excess rows before using arrayformulas... for example if you have 100 rows of data make sure you don't have more than 200 rows in your speadsheet... (highlight right click and delete excess rows) The following formula will do what you are asking for assuming row 1 is headers.
Paste the following in cell F1
=arrayformula({"Final ...
0
I am grabbing the last 365 valid trading days as reported by GoogleFinance then with array_constrain we are basically saying grab the last 55 trading days to get the highest.
=query(array_constrain(GOOGLEFINANCE("F","high",(TODAY()-365),TODAY()),55,2),"Select Max(Col2) label Max(Col2)''")
You do not have to use Query so you can do something like this:
=...
1
I wonder why this question hasn't got a decent answer yet after more than 4 years.
It's surprisingly simple :-) Just create a new script (standalone, or under another spreadsheet), and replace all the SpreadsheetApp.getActiveSpreadsheet() calls by SpreadsheetApp.openById("spreadsheet-id") in which the spreadsheet-id is the last part of the document URL; ...
1
I expect you have already solved this, but still wanted to post my solution for others who might have the same issue.
I edited user0's answer to allow for dynamic criteria
function onEdit(event){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getSheets()[0]; // 0 == first sheet.
var cellRange = SpreadsheetApp.getActiveSpreadsheet()....
0
As of today's version of Google Sheets (on Dec 6, 2019), we can do this to make the values appear:
Create your pie chart (assuming you know how)
Click in the upper right of the chart, on the 3 dots. "Chart editor" will appear on the right.
Under "Chart editor", there will be "Setup" and "Customize". Click on "Customize"
There should be a selection for "...
1
You can use this script and bind it to a button:
function transferCellValueOnButtonPress() {
const ss = SpreadsheetApp;
const selectedCell = ss.getCurrentCell();
const selectedCellValue = selectedCell.getValue();
// Target cell
const targetCell = ss.getActiveSheet().getRange("C2");
if (!(selectedCell.isBlank() || isNaN(...
0
You are trying to update the value AND the background colour of a cell when a certain "DMC" number is entered. DMC information is held on a sheet ("DMC no.") and consists of cells with given numerical values and specific background colours.
Revising the DMC data format
There is no function that enables Google Sheets to get the background colour of a given ...
0
SOLUTION:
Open about:config
Search forbrowser.link.open_newwindow
Set value to 0
Restart Firefox
Go to options and uncheck "Open links in tabs instead of new windows"
Restart Firefox
Go to options and check "Open links in tabs instead of new windows"
Restart Firefox
You may be able to just set the value to "3" instead of zero, but since I am not sure what ...
0
CMC change your structure. The new code to use this is:
=IMPORTXML("https://coinmarketcap.com/currencies/bitcoin/", "//span[@class='cmc-details-panel-price__price']")
1
Instead of =VALUE(A1) use =TO_PURE_NUMBER(A1)
Reference
TO_PURE_NUMBER
0
=UNIQUE(
TRANSPOSE(
SPLIT(
CONCATENATE(ARRAYFORMULA(UNIQUE(Week1!B2:B)&CHAR(9)))&
CONCATENATE(ARRAYFORMULA(UNIQUE(Week2!B2:B)&CHAR(9)))&
CONCATENATE(ARRAYFORMULA(UNIQUE(Week3!B2:B)&CHAR(9)))&
CONCATENATE(ARRAYFORMULA(UNIQUE(Week4!B2:B)&CHAR(9))),
CHAR(9))
)
)
Props-Will Cervarich
...
0
Multiply by 1 and then evaluate >0.
This may help anyone who wants to have zeroes in the list but still want to evaluate if greater than zero.
0
Not sure if this is still an issue, but Windows store now has an app that you can download. Docs for Google documents charges you 3.99 for 10 licenses.
0
You want to build a query where certain criteria are triggered by selection of checkboxes. You have already set up formula to create dynamic lists of the checked criteria.
The data consists of:
the primary table (4 columns x 15 rows)
Criteria 1: Type - 3 x checkboxes, and 1 x dynamic list
Criteria 2: Team - 3 x checkboxes, and 1 x dynamic list
Criteria 3: ...
0
Try:
=if(B1="partner",importrange("<insert the url for spreadsheeth#2>","wa_13682302!A1"),"")
You need to combine:
IMPORTRANGE: to get the value of Cell A1 from Spreadsheet#2 doc ref
IF: to create an expression to test the value in cell B1, and then return values based on whether the expression is true or false. doc ref
2
Use on edit / on change triggers and the corresponding event object.
Example
function onEdit(e){
var a1Notation = e.range.getA1Notation();
e.source.toast(a1Notation + ' was clicked');
}
Related
How to trigger a script in Google sheets when a value changes
References
https://developers.google.com/apps-script/guides/sheets
https://developers.google....
1
From my answer to Measurement of execution time of built-in functions for Spreadsheet
Google Sheets doesn't include a built-in tool to measure the
recalculation time.
One alternative is to use the Chrome Developers Tools Timeline
but bear in mind that functions like IMPORTHTML and IMPORTXML are not
recalculated every time that the spreadsheet ...
1
The specific problem was this:
Table RawInventory use a query in each row:
=query(TreeT,"select C where E = '"&UPPER($F14)&"'",0)
Where TreeT is a named range.
But TreeT had blank lines at the end for expansion. (If you insert a line, the range definition updates. If you append it, it doesn't.)
If F14 was blank, query would return two values, ...
2
Please place the following formula in cell H1:
=SEARCH("-",$D1)
0
You have grade reading data sourced from another spreadsheet using IMPORTRANGE. You want to use that data to analyse the books being read by grade.
There are many ways to achieve this outcome. This answer covers the basics of two methods.
Assume that we have data in a spreadsheet, and this data is imported into the current spreadsheet using IMPORTRANGE.
...
0
Both Google sheets and the TYPE formula are correct giving the correct result.
Your screenshots do not complement each other.
Were they to be right, when you have the format at Number ->1.000,12 the number returned should have been 2.20 and not 2.2 as shown here in your screenshots.
Most likely your Locale is wrong. Try changing it by going to File -...
-1
That is a great question
If you wish to make a entire row a certain color
Insert Checkbox
Go to Conditional Formatting
Add a Rule
Make the drop down Custom Formula
For Value or Formula Type =$A1=TRUE this will cause whatever range you set to turn to that color. If you want the default color to be Red when not selected.
Add a New rule
Repeat steps 4-5, But ...
0
I really like the answer provided by user79865. If you want to use it and have locale different than US (like my Polish one) you may have different separator for function arguments in Sheets. And instead of:
=QUERY(A3:F13, "select "&GetHeader("Type")&" where ("&GetHeader("Version")&" = 'Version 1') and ("&GetHeader("Type")&" <> ...
0
The same occurs in Excel Ref Excel number format to only show decimals when necessary, unfortunatelly the conditional formatting options in Google Sheets don't include numbering formatting.
What about this simple workaround?
Set number formatting to Automatic
Add and empty string to the result of the original formula:
=""&if(F7+J7+O7+S7+Y7+AD7+AH7;...
0
If your number is 3.14159265 you will get 3.14159265, if you want less there is no way to have 3.14 and have no ".00" in whole numbers.
You cannot achieve it with no auxiliary column where you use trunk(A2,2) and then use automatic in the format for the result
I just changed the format of column J in your example file to "automatic" and numbers are shown as ...
0
You must think all the possible cases and build a sequence of IFs, so =IF(case1,"result_case1",IF(case2,"result_case2",...IF(caseN,"result_caseN")))
make a list of all possible cases you want to find and then build the logic, then translate it into formulas.
You can use different cells to try and see the different results, once you got it working you can ...
0
To add text to a value you need to use another column, say your column A has the values, you want to add , in B2 (assuming B2 is a header) just enter:
=A2&"<KB>"
or if you want a space in between
=A2&" <KB>"
So, if you have in A2 "1024" you will get:
1024 <KB>
0
=iferror(if(G4="","-",(F4*0.25)+G4),"invalid shipping quote")
Dash when no quote, and message when quote has a value but is not a number (just in case there is an incorrect entry)
0
You probably have the file open in another browser tab. So when you are looking at one it shows the user (you) is possitioned in that cell
0
="C"&counta(C:C)+1 maybe? need to add more characters to be able so save the answer
0
Ctrl+D works if you select the first cell too. In excel, if you ctrl+D in one cell it understands the cell above is the original, if you select 2 or more it takes the first selected as the original. Google sheets does the same except the 1st mentioned case. You always have to select the original data cell too
0
You can easily hide a column or a row either changing the size to 0 or groupping it and then hide it. To do something like hiding all users save for user 2 (for instance) you'd have to build a script that first identifies the user and then hide all rows and then show the row where that user is. It's not simple and easy.
On the other hand you could (should) ...
0
mmm, ok, (using formulas) first thing you need to remove the file name from the path, then identify how many "/" you got and count the characters so you know how many characters are from the prev-to-last "/", then use =left()-amount_o_chars and then add back the file name.
I suggest you do things step by step. Each step in a different cell using the data ...
0
Replace
"Form Responses!a2:M100"
by
"Form Responses!a2:M"
Related
Query multiple data input ranges into 1 column output
SUMPRODUCT inside ARRAYFORMULA in Google Sheets
0
"Could not fetch URL" basically means that Google is not able to get the content of the URL. It's be very likely that this is caused by "something" on the side of the specified URL
Google use the same group o IP addresses for many services and users. If the service is popular among Google users it could block Google IP addresses temporally or permanently. ...
1
Use conditional formatting with the following custom formula: =B2=MAX(B$2:B$4). Change the cell names to suit your use case. B2 indicates the first cell in the selection and B$2:B$4 is first column in this example.
Source
0
There are several ways that you could adapt your script to sort a different column for each sheet.
For example, you might create a series of IF statements that combine the sort column and the sheet name. Another option, and the one used in this script, is to use the Javascript switch statement (docs).
Though an IF statement is still required, the value ...
1
onEdit / onChange triggers aren't triggered by formula result changes, they are only triggered when a user edits the spreadsheet by using the Google Sheets UI.
The solution is instead of monitoring for formula results changes look for the cells being edited by the user whom will make the formula results change.
0
It seems the page content can not be parsed.
=IMPORTXML("https://shopee.sg/Ice-Silk-2019-Men-Underwear-Cool-Breathable-Comfortable-Sport-Bamboo-Fiber-Boxer-Stretchable-Brief-i.15618985.2902337324","//")
returns #N/A
I'm using the Chrome extension https://github.com/maximelebreton/quick-javascript-switcher to switch off the JS and check what data are still ...
0
You can wrap your formula with the IFERROR function instead:
=IFERROR(ArrayFormula(JOIN(", ",UNIQUE(FILTER(SOURCERANGE!A1:A99999,SOURCERANGE!N1:N99999=A2)))),"")
You could also try the IFNA function.
2
You can replace both your formulas with this single one:
=SUM(FILTER(B2:B,A2:A>(TODAY()-E1)-1))*POWER(COUNT(FILTER(B2:B,A2:A>(TODAY()-E1)-1)),2)
Functions used:
FILTER
TODAY
POWER
Pro tip
Use the following formula without the need of an extra cell. It also takes into account leap years.
=SUM(FILTER(B2:B,A2:A>(TODAY()-(TODAY()-(EDATE(TODAY(),-12)))...
1
Bypass it's not possible, you could
use filter views,
create links to jump directly the corresponding row
You could create the links before hand
You could use Google Apps Script to create a web application that creates the link
0
Convert your custom function to an add-on will not solve the problem because custom functions only can return values. They can't make changes to the spreadsheet, they can't call services that require authorization to be executed.
References
https://developers.google.com/apps-script/guides/sheets/functions
0
More specifics on Vidar's answer:
An anonymous user (accessed via link) can use the script attached to the sheet when they are given edit rights AND they sign-in with a google account, and give the permissions to run. So an anonymous user without a gmail account will not be able to use the scripts.
To allow anyone to use the script it has to be published ...
0
=arrayformula(sum(if(D10=AB11:AB21,D21,0),if(I10=AB11:AB21,I21,0),if(N10=AB11:AB21,N21,0)))
Found the solution myself! I do not 100% understand how arrayformula works - but I got it to work how i needed it to so ill take it. Hopefully this helps if someone else is looking for something similar!
0
You cannot copy and paste a cell with the attached comments but you can cut and paste a cell.
0
To avoid have to edit manually the formulas, instead of using "hardcoded" (typing the date) conditions you could use references to a cell that contains the conditions. I think that it's easier to handle this using QUERY than appending several FILTER functions.
Example
The following formula filter the range A:D where dates in column A are between the date ...
1
Tl;Dr: Yes, it's possible.
With Google Apps Script you could read/write Google Sheets data. To effectively use it you should know "some" programming, "some" JavaScript and apply "some" "spreadsheet thinking".
By "spreadsheet thinking" I mean that you should know how spreadsheets handle values and formulas, and understand how values (single and arrays) are ...
0
=query(range;"Select A,min(B), max(B) group by A label min(B) 'Min Date', max(B) 'Max Date' ")
1
Maybe your Google regular/commercial account reached a quota
From OAuth API Verification FAQ
Users seeing the Unverified App Screen or "Sign-in with Google temporarily disabled"
Why are users seeing this?
To protect users and Google systems from abuse, unverified apps that are accessing restricted or sensitive scopes have a 100-user quota ...
Top 50 recent answers are included
