New answers tagged google-sheets
0
As mentioned in the comment above, it's not possible to retrieve the text via importxml. I managed, however to scrape it successfully. Here's how:
Open developer tools (Ctrl+Shift+i or File → Developer → Developer Tools)
Go the site in question.
Click on the network tab -> Filter icon -> select XHR to filter out some of the requests
Find the relevant ...
0
The answer is importrange
Example: importrange("url", "range")
Thanks to @marikamitsos for helping out.
0
The issue to your question has nothing to do with the IMPORTRANGE function.
It is about your reference to the sheet.
You need to enclose the name of the sheet in single quotes, because the name of the sheet itself has a blank space between the words.
As from the official help site on "Get data from other sheets in your spreadsheet":
If a sheet ...
-1
Editor's note: This answer is explaining how to use the script shared in another answer
For other non-expert people like me, in Google Sheets:
Go to tools>script editor
Clear everything, then paste the script
At the "THIS_SHOULD_BE_YOUR_FOLDER_ID" you have to insert your Folder ID (Just the ID, but leave the "". You can find the ID if you open your folder ...
0
It's not possible to import range by adding a formula in the Pivot Table editor, you have to add there a reference to a range in the current spreadsheet.
If you don't want that the sheet with the imported data be visible you could hide it.
Another alternative is to use Google Data Studio. Depending on the purpose of your pivot table, you could use GDS to "...
0
You can use the same QUERY formula (with minimum alterations) to get your results for all 3 tabs.
The formula to be placed in A1 of your Month 3 tab would be:
=query(Subscriptions!A1:K, " select * where D matches '(1|2|3).+' ",1)
Making a minor change in the query -change '(1|2|3).+' to '(1|2).+' or '(1).+'- you can use use the same formula for tabs ...
0
The formula you would use is:
=ArrayFormula(SUM(IF($B2:$B27=K2:K27,$B2:$B27,"")))
Functions used:
ARRAYFORMULA
SUM
IF
0
You just need to include another statement in your IF:
=ARRAYFORMULA(TEXTJOIN(", ",TRUE,IF(((Orders!C:C = "Visa")*(Orders!B:B=E1)),Orders!A:A,"")))
We use the * character because we cannot have AND when using the arrayformula
-1
Just something that i found especially if you find that you have done everything correct with your syntax and links. When attempting to import multiple ranges or sheets, it could be throwing this error due to a lack of permissions granted. It is likely that you have link-sharing turned off for the source spreadsheets that you desire to import and query. In ...
0
From the question
Is it possible to reference filtered data from one sheet to another in google sheets?
I am using the filter button to sort in a master sheet. I am then using this formula in a new sheet: =IMPORTRANGE("urlofthemastersheet", "sheetname!A1:A")
It's not possible by using IMPORTRANGE but you could import that data by using Google Apps ...
0
The following method works, and is not too arcane:
A: I defined 3 named ranges, just to make my formulas easier. This sheet is called 'Fix' so the named ranges use Fix as a prefix.
FixDataToChange is the stuff with the errors in it.
FixRegex (Yellow) are the patterns to use. Most of them are simple text patterns.
FixChangeTo is the material to replace ...
0
I tried you example changing the formula this way:
=IF(C7=TRUE; REPT("l";A140);"")
Last “” is the “else” statement, when C7 is false. This way I can make it works.
Cheers
1
There isn't a "pretty print" built-in feature for Google Sheets formulas. IMHO opinion the simplest way to improve formula readability are those already described in How can I pretty-print a formula in Google Sheets? and How to build formula in Google Sheets using content of other cells:
Insert spaces and breaklines
If you edit the formula to introduce ...
0
Google Sheets has a built-in feature allows to set formatting rules, it's called Conditional Formatting, but it doesn't has a way to set the comparison of the current value with an old value.
One alternative is to use Google Apps Script, another is to use an add-on.
Using Google Apps Script
If you decide to use Google Apps Script, you could use an on ...
0
I recently had to do this and was able to use search/replace tools.
Select the cells with image URLS.
Edit -> Find and replace (ctrl+H)
Search expression: (.*)
Replace expression: =IMAGE("$1")
Check boxes:
Match entire cell contents
Search using regular expressions
0
I think I solved the problem: IF is not needed. I removed it and the new formula worked:
=MATCH(A1,INDIRECT("'Master'!$A$1:$A$60"),0)
0
Someone gave me the answer, but on the wrong question, so I'm posting it here.
=INDIRECT("A"&(51-(48-(COUNTIF(Info!A5:A52,"*")))))
0
Isn't it always the case. You spend hours trying to figure something out, then as soon as you ask someone the answer arrives. For anyone wondering, here is the correct code.
function store() {
var ss = SpreadsheetApp.getActiveSpreadsheet ();
var source = ss.getRange ("Sheet5!D4:D80");
var destSheet = ss.getSheetByName("Sheet1");
var lastCol = ...
0
As of Feb 2019, you can now do this with the Google Docs REST API in addition to Google Apps Script as mentioned in the other answers.
Here's a blog post I wrote linking to the video, page in the docs, and a working (Python) sample app I created that does exactly what the OP requested, mail-merging data from Google Sheets into a form letter created via ...
1
You can't order a query by month names because that would order them either alphabetically from A - Z or from Z - A. To achieve your ordering from January to December, you first need to create a mapping as follows:
1: January
2: February
...
12: December
Then you can order by month number to get the sorting correctly.
0
Simple solution (for data only).
These steps remove hidden code causing the extra line in the cell:
Select the cell that is formatting oddly
Select and copy the text in the formula bar
Click in a different cell, then click back into the oddly formatted cell
Paste the contents back into that cell
0
One problem with you formula is that the url is enclosed between < and >, remove these characters.
Regarding your function, the arguments in masterCal.createAllDayEvent(title, location, description,date) have not being declared.
You could save a lot of time while writing your scripts / fixing them by following the recommendations of https://...
1
As of today, you can do a simple Ctrl-F and find it across sheets. In case it's hidden, you get a notification saying that the search result matches a hidden cell.
0
You want to insert a formula into Column R when a form submission is received.
As outlined in the comments:
Use an onformSubmit trigger to take action when a new submission is received. This is installable Trigger, so you will need to ... install it.
Take advantage of Event Objects to get the row number of the submission. In the script you'll note the ...
0
To solve the error
change
spreadsheet.getRange(dest).activate();
to
dest.activate();
0
There are two ways to use dates alongside SUMIFS
either using the DATE function
=SUMIFS($C$1:$C$10, $A$1:$A$10, "=bob", $B$1:$B$10, "<="&date(2019,12,25))
or the actual date
=SUMIFS($C$1:$C$10, $A$1:$A$10, "=bob", $B$1:$B$10, "<12/25/2019")
0
This feels hacky but suffices in the short run for my purposes:
=date("2020","01", left(A1,2))+TIMEVALUE(right(A1,8))
(And then Format > Number > More Formats > Custom number format > "yyyy-mm-dd hh:mm".)
But hardcoding the year and month feels wrong.
1
If you're trying to SUM() a column based on criteria, then SUMIF() or SUMIFS() would get you what you want. In this example you could use SUMIFS()
=SUMIFS(P:P, A:A, "PG", F, "Indexed") - this gives you a SUM of values in P where Column A = "PG" and Column F = "Indexed". You could extend these criteria for as many filters as you need.
You'd then change this ...
0
Short answer: Yes, it's possible to copy a range from one spreadsheet to another
It's very likely that your googling didn't give you the results that you are looking for because you are not using the terms commonly used: spreadsheet and workbook, actually, the Google Apps Script use spreadsheet for the file and sheet for the sheets that it contains.
You ...
2
Select All (Ctrl+A) and go to Format → Conditional Formation.
This will show you every active conditional formatting rule even if some cells are hidden.
1
You can do it by creating a new filter view and filtering out eggplant
Go to menu > Filter views
Create new filter view
Deselect eggplant
OK
After creating the filter view, within the chart setting you are given the choice to Include hidden/filtered data
1
You can use the following formula in cell AE4
=ARRAYFORMULA(QUERY(TRANSPOSE(QUERY(TRANSPOSE(FILTER(B4:Z,B3:Z3=DATE(2019,12,16))),
"select "&TEXTJOIN(",", 1, IF(LEN(A4:A),
"avg(Col"&ROW(A4:A)-ROW(A4)+1&")", )))&""),
"select Col2")*1)
(If this is what you are looking for, I will update with image and more info as well)
0
How about something like this? If you need more help, please share a spreadsheet with your input and desired output.
0
Select your data and create a pivot table
1
If the cells all contain ".com" or "www" or "http" etc. I just do a simple find and replace.
Find "www" -> Replace with "www"
Search range or column etc.
Replace all
0
You can create an easy macro for this
Go Tools -> Macro -> Record macro
Click Save and give this macro a name
Click EDIT SCRIPT or Tools -> Script editor to open the script editor
Replace the content of this macro function with the following code:
var spreadsheet = SpreadsheetApp.getActive();
var values = spreadsheet.getActiveRange().getValues(...
0
Perhaps the simplest solution is to copy the sheet that you want to keep to another to a new spreadsheet. Unfortunately, there is no way to avoid that editors be able to create new sheets but there are several workarounds.
Use a third-party app like Awesome Tables or Google Apps Script to create front-end for your spreadsheet.
Use the Google Sheets API to ...
0
You can use Clasp: clasp allows you to develop your Apps Script projects locally. That means you can check-in your code into source control, collaborate with other developers, and use your favorite tools to develop Apps.
1
To create a custom function use Google Apps Script. This is very similar to writing a JavaScript function.
/**
* If the arguments are numbers, the sum of will be returned
* If one of arguments are strings, they will be concatenated
*
* @customfunction
*/
function myCustomFunction(a,b){
return a + b;
}
Start by reading
https://developers.google....
0
Best way is to send an email using Code.gs from the Google Sheet to automatically send the student an email with the pass/fail result.
0
The use of the ARRAYFORMULA is what you need.
Please try the following formula instead
(Note adjust the ranges J3:J11 and K3:K11 to your needs and since you have tick-boxes change 1 to TRUE)
=ArrayFormula(IF(J3:J11 = TRUE,IF(ISBLANK(K3:K11) = TRUE,"error","ok"),"ok"))
Please read more about ARRAYFORMULA
0
This function gets rid of all non-digit characters from cell A1 with REGEXREPLACE(). To avoid error when there's no non-digit character, the formula is wrapped with a fall-back IFERROR(). Output strings are then cast as a number with VALUE().
=VALUE(IFERROR(REGEXREPLACE(A1,"[^0-9]",""),A1)
The output for the sample from above is:
4237004000
9002570000
...
0
You can access a cell or a range from another google sheet (i mean a separate file) using the the IMPORTRANGE function. Here is an example:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1Aviydfsh6PygtPjphKc5Fnvnm8g_Xoedf6utNqp2Ql4A", "MySheetName!E107") + SUM(E4:E99)
Here we provide the url to the other sheet in the IMPORTRANGE function, fetches ...
0
Your function doesn't returns a boolean because Google Apps Script doesn't support String.prototype.includes() in the same way that it doesn't support several of the additions introduced in ECMAScript 2015
Related
Google Apps Script Javascript compatibility error
can't locate ; syntax error
Reference
Basic JavaScript Features
0
The default values for checkboxes are TRUE and FALSE. If your checkboxes use the default values, the conditional formatting custom formula should look this way:
=A4
In the context of the conditional formatting feature of Google Sheets that means apply the conditional formatting when the value of A4 is true. Instead of A4 you should use as reference the ...
1
You can use the following formula in cell A4.
=ArrayFormula(IF(
ARRAYFORMULA(SUMIF(IF(COLUMN(E4:J4),ROW(E4:J8)),ROW(E4:J8),E4:J8)<132)*
ARRAYFORMULA(SUMIF(IF(COLUMN(E4:K4),ROW(E4:K8)),ROW(E4:K8),E4:K8)>=132),1,"Oh no!"))
(The above formula assumes data is in E4:K8. Please adjust it to fit your data.)
The following formula can be used as an ...
0
I had the following situation:
I had the botanical name. I wanted the common name.
The shot above is from a sheet labeled Trees, so I defined two more Named Ranges as TCommon which was column E3 to end, and TBotanical which was column F3 to end.
(By using a prefix from the sheet name, all of the named ranges for that sheet are together in the NamedRange ...
0
Nowadays there is the Google Sheets have a "protection" feature that could be applied to sheets and ranges.
To apply it automatically you could use Google Apps Script and a time-driven installable trigger.
Reference
https://developers.google.com/apps-script/guides/sheets
https://developers.google.com/apps-script/guides/triggers/installable
0
After importing a csv file with a column with different decimal values and applying Tom Woodward solution I got some crazy numbers. What worked for me was to select the columns and click first on Format>Number>Plain Text & second Format>Number>Number . Aparently some of the decimal values where considered dates after importing the csv file..(...
1
As you're only summing a few columns, you could do something like this:
=SUMPRODUCT(ARRAYFORMULA((E4:E150+F4:F150+G4:G150+H4:H150+I4:I150+J4:J150)<132),ARRAYFORMULA((E4:E150+F4:F150+G4:G150+H4:H150+I4:I150+J4:J150+K4:K150)>=132))
Using ARRAYFORMULA with functions that already take a range gets overly complicated. I think this solves it for you fairly ...
Top 50 recent answers are included
