What are /r/sheets' favorite Products & Services?

From 3.5 billion Reddit comments

You need to use parenthesis like this:

WHERE EC = 'Cheifs' AND (CE = 'C' OR CE = 'RG OR ...)

Or you can use a different form of matching:

WHERE EC = 'Chiefs and CE matches 'C|RG|RT|LG|LT'

"matches" is an SQL like option in QUERY that lets you have multiple choices (OR using |). More here: https://developers.google.com/chart/interactive/docs/querylanguage#where

Try this out -- https://zapier.com/apps/google-calendar/tutorials/how-to-create-calendar-events-from-spreadsheet-tutorial

Found the problem, my extension CanvasBlocker. I realiced it after using incognito mode to check the sheet and realiced i could edit it anyway despite being log-off.

​

After removing it this works smooth as a kitty

HTML e-mail can tough to pull off, particularly if you want to include JavaScript.

This article over at Mailchimp says:

>the vast majority of email clients block scripts since they can hide malicious content.

Can you send the file as an attachment?

Or can you host it somewhere?

pretty much any ascii or unicode.

For the most part, just search ascii star and something will show up.

You'll find crappy sites like this to copy from.

Hey, I know of no way to do this in Google Sheets. The charting functionality is quite limited. I read through the chart api documentation and cannot find a simple workaround customization either. https://developers.google.com/chart/interactive/docs/gallery/linechart

Query's docs suggest the Google Visualisation API Query Language Reference.

There it offers comparators of "contains", "starts with", "ends with", "matches", and "like" and corresponding examples; check the docs for what each of the examples would match to:

where name contains 'John'
where dept starts with 'engineering'
where 'cowboy' ends with suffix
where country matches '.*ia' where name like fre%

Yeah sure. For example: https://www.amazon.com/Novel-Stephen-King/dp/1501175467/ref=mp_s_a_1_5?ie=UTF8&qid=1530235328&sr=8-5&pi=AC_SX236_SY340_FMwebp_QL65&keywords=it&dpPl=1&dpID=41aCsKYeDwL&ref=plSrch.

The URLs are product pages such as this Amazon link, and for the text I typed the price. I'd like to sum up those numbers but I'm not sure how to do it when the numbers are hyperlinks, the sum just comes out to 0.

1. Yes if you add items to the existing lists this will be included
2. Learn: Learn JavaScript and watch videos on this channel.
3. 4 Meals & Specific day: Needs rewriting parts of the script.

From a quick glance Swit or Collavate seem to do what you need.

It looks like they're blocking it. They also closed down their API, which is a shame.

One thing you could do is use IFTTT to track price changes.. but I think that would require one applet per product, which might be annoying.

You might want to use IFTTT.com for this. I've got it posting the weather every morning to a sheet that I then reference with IMPORTRANGE.

I believe this one should work

I forgot that IFTTT is limited to five 'applets' for free accounts. If you consolidated the feeds into one RSS feed using another service, you could get around this.

Basically, you'd make an account and go to this recipe, pop in the feed URL, hit 'connect' on the Sheets part and give it permission.

In the recipe, it'll ask for the path of the spreadsheet -- If you want it in your main folder, you can enter 'comicSheet' or whatever you want to name it. If you want it in a folder, do 'folderName/subFolderName/comicSheet'

For the items, the default options are most likely what you want.

Anyway, in your sheet, this is for dinos

=ARRAYFORMULA( --REGEXREPLACE( IMPORTXML("https://www.qwantz.com/rssfeed.php","/rss/channel/item/pubDate"), "\d+:\d+:\d+.*",""))

Are you creating an archive or emulating a newsreader? IMPORTFEED won't save the output of the feed and most feeds only serve five items at a time.

This was reported on the official Google forums this morning too; after some brief panic, it appears to be back up though. The listing shows an update date of today, so my guess is that this was just removed briefly so they could make that update.

https://workspace.google.com/marketplace/app/google\_analytics/477988381226

You’d probably be better off trying out their API.

https://docs.cloud.coinbase.com/prime/docs/rest-api

Then get the add-in to import REST API to Sheets.

https://workspace.google.com/marketplace/app/api_connector/95804724197

Another option if you wanted to use Tasker is you could just click a screen widget and read the odo reading in by voice and upload it to a spreadsheet automatically.

That weather station didn't seem to report anything.

Try this out using a nearby station

=QUERY( IMPORTDATA("https://www.wunderground.com/weatherstation/WXDailyHistory.asp?ID=KDEWILMI8&month&day&year=2020&format=1&graphspan=year"), "select * where Col2 is not null")

replace KDEWILMI8 with another weather station either in your browser or the formula. KILG didn't return any values, though.

... or to narrow it down by month

=QUERY( QUERY( IMPORTDATA("https://www.wunderground.com/weatherstation/WXDailyHistory.asp?ID=KDEWILMI8&month&day&year=2020&format=1&graphspan=year"), "select * where Col2 is not null format Col1 'YYYY-MM-DD'"), "select * where Col1 is not null and Col1 >= date '"&TEXT(DATEVALUE("3/1/2020"),"yyyy-mm-dd")&"'")

This is the cleanest method I could find. They locked down API keys to weather station owners, which is a shame.

You can use the IMPORTJSON script. Run the script and give it the permissions etc.

From there, use this:

=IMPORTJSON("https://www.duolingo.com/2017-06-30/users/15096477","/courses/title,/courses/xp","noHeaders,allHeaders"

Japanese 2748 German 4710 Spanish 8181 Ukrainian 40 Italian 1452

Found the place where the data is kept but now I don't know how I can get it.

It's here and I would like to get the xp parts, is there an easy way to do so?

If you can scan the id based on barcode or qr then maybe Appsheet (connects to google sheet) is something to work with. App on the phone / tablet to scan the id (and maybe the equipment as well)

Order capture

Inventory Management

Which OS are you using? Something like ShareX can capture a specific area.

Another option would be printing a specific range to PDF, then to run some sort of watchman script to convert it, etc etc.

I think using sharex to capture the set coordinates is the easiest solution. If you're on another OS, we can probably sort out a slick solution.

https://getsharex.com/

>=Image(importxml("
>
>https://www.gametracker.com/player/%3D%5BdG%5D%3D%20Squishy/jailbreak.csgo.distinction-gaming.org:27015/
> >”, “//*[@id=‘graph_player_time’]/@src”))

I appreciate that, but do you have an ideas on how I could automatically take the data from that image? Also, would that automatically update?

Can this be done with Gsuite? Yes. But Gsuite really isn't the place I like to go when it comes to printing things. IMHO printing has always been an afterthought at best with anything Google related.

Have you checked into LibreOffice? I know it's word processor (Writer) has hundreds of label templates built into it and there are many YouTube tutorials on merging a spreadsheet (Calc) of addresses into labels in Writer.

Thanks for the reply and I am going to try and use it for a higher volume so I tried using the formula you gave - ImportXML(CONCATENATE("<code>https://search.yahoo.com/search?p=</code>", B3,"&n=1"),"//a") - but It's not getting the URL of the first website and instead takes all the values (the home button the different search suggestions etc.) instead. Thanks for the help

JSON and XML are very similar -- it all requires the full path, so you need to follow the path back.

The main thing is that its case sensitive.

Scraping is pure CSS and can be a total pain in the ass at first. The key is to remove all of the nth-of-type business. Anyway, maybe dig through these to see if any of them are useful -- https://www.producthunt.com/search?q=scraper

A friend suggested those. No idea if they're good. For this sort of scraping we rely on browser rendering.

Since you asked so nicely:

https://www.appsheet.com/start/407b73d1-aa28-4ae6-9ee4-c088805fc737#appName=SalesLeadTrackingApp-77287&amp;page=graph&amp;sort=%5B%5D&amp;table=Leads&amp;view=By+Region

Well, you would need to pretty it up with sheets and InDesign seems like a good idea. Maybe not worth the subscription fees Adobe charges now.

You might be able to batch the work with Scribus, a free open source solution. Check it out. https://www.scribus.net/

Best way I can showcase my problem is with a short 1 min vid, if you can figure out the issue it'd be appreciated : https://www.loom.com/share/0aa15f8db0d04e8395e63e1fb4976f80

having adjectives in B2:B and animals in C2:C, you can use =PROPER(index($B$2:$B,randbetween(1,counta($B$2:$B)))&index($C$2:$C,randbetween(1,counta($C$2:$C)))&RANDBETWEEN(10,99) to create Tinysnail40

That being said, password managers like BitWarden or 1Passwordare fantastic and are pretty cheap. For main passwords I prefer using song lyrics that somewhat relate to the site.. but that might not work for kids.

edit: made the ranges static.

try this: select everything > conditional formatting > =ISBLANK(A1) or =NOT(LEN(A1))

... but for the best performance, though, check out Dark Reader. This is by far the easiest way to handle this if you want a true dark mode. This works for pretty much every site, too, which is really nice.

Yes. In Vivaldi you can change the keyboard short cut to a different key(s) or disable the keyboard short cut altogether.

• Click on Vivaldi icon
• Click Tools > Settings
• Go to Keyboard section
• Modify shortcuts in the various sections

Below is the link to the Vivaldi website for more details:

https://vivaldi.com

Hope that helps.

I've made the app public (I think), are you able to run this?

function pull() { var url = "https://script.google.com/macros/s/AKfycbxdeZftodmvbzqdNMpOUuA0-vyERH2UQ05hGfH_kXY/exec" var queryString = "?row=2&col=2"; url = url + queryString var request = UrlFetchApp.fetch(url).getContentText(); Logger.log(request); SpreadsheetApp.getUi().alert(request); }

Ok, so that gives literally just 'b' as the output of the web app, which is good I suppose.

But on the spreadsheet side I still don't know how to access 'b'. The code still returns that massive output:

function test() { var url = "https://script.google.com/macros/s/AKfycbxdeZftodmvbzqdNMpOUuA0-vyERH2UQ05hGfH_kXY/dev?row=2&amp;col=2"; var response = UrlFetchApp.fetch(url); Browser.msgBox(response.getContentText()); }

I've made a web app that just queries a cell (by row, col) of a spreadsheet.

function doGet(e) { var sprd = SpreadsheetApp.openByUrl("url"); var dataSheet = sprd.getSheetByName("Data"); var result = dataSheet.getRange("TestRange").getCell(e.parameter["row"],e.parameter["col"]).getValue();
return HtmlService.createHtmlOutput(result); }

If I use the web app url in chrome, https://script.google.com/macros/s/AKfycbxdeZftodmvbzqdNMpOUuA0-vyERH2UQ05hGfH_kXY/dev?row=2&amp;col=2, I can see the correct answer displayed, which is 'b'.

But I don't know how to access 'b' from a script. If I do what you suggested above:

function test() { var url = "https://script.google.com/macros/s/AKfycbxdeZftodmvbzqdNMpOUuA0-vyERH2UQ05hGfH_kXY/dev" var queryString = "?row=2&col=2"; url = url + queryString var request = UrlFetchApp.fetch(url).getContentText(); Browser.msgBox(request); }

I don't know what to do with request. It's just a whole bunch of crap.

if the back-end sheet contains sensitive data, a web app is probably best. I'm no expert, but you could probably do a urlfetch and can send data with the request, which this page has excellent info about sending data,

https://developers.google.com/apps-script/guides/web

such as...

function doGet(e) { var url = "https://script.google.com/macros/s/AKfycbzM7qMyGhxx81FiTqpNjG6Sdr4a1HmiCAVsB3Wahdk/exec" var queryString = "?infoImSending="+theInfoAsVar url = url + queryString var request = UrlFetchApp.fetch(url).getContentText(); //var request will contain the return info }

from there, to get a return string, from the webapp you could end the web app with....

return ContentService.createTextOutput(variable);

which will give the var variable back to the script that called the web app

Not immediately obvious what is going on, you would have expected that when the sheet was opened then the number value would have been saved, and that the script trigger would return the previously-stored value.

&#x200B;

Do you know the full formula that was being used and which generated the #NAME error.

&#x200B;

The other thing that has occurred to me is that the weekly script might be failing - this may be because it doesn't have permission to access files which aren't open. Can you check your script execution and see if there are errors (or failures when the script is triggered) (look at https://script.google.com/home/triggers)

Before "where" add:

select *

So:

"select * where Col4 contains 'In progress'

You can replace * with Col1, Col2. That way you specifically get the columns you need. https://developers.google.com/chart/interactive/docs/querylanguage

its not identical, but its similar. This has most of what there is to use.

You could wrap it in a second QUERY to pull the average for the second column

=ARRAYFORMULA( QUERY( QUERY( {INT(C2:C), TEXT(C2:C,"hh")}, "select Col2, Count(Col2) where Col1 = "&TODAY()&" group by Col2 order by Col2 label Count(Col2) ''"), "select Avg(Col2) label Avg(Col2) ''"))

mess around with it -- you should get a good handle on it in no time.

Presuming that your input data table is at the top left of a sheet named "Input", with "Line Item Name" in column B and "Line Item Quantity" is column E,

Put the following formula is cell A1 of another sheet:

=QUERY(Input!$A:$E,"SELECT SUM(E), B WHERE B<>'' GROUP BY B LABEL SUM(E) 'Bags'",1)

The result is a table with total number of bags required for each unique Line Item Name, alphabetized by Line Item Name.

&#x200B;

Explanation

The QUERY function performs a SQL-like database query on a range of spreadsheet cells.

Parameter 1: Input cell range

Parameter 2: Query text

Parameter 3: Number of header rows in Input cell range

See https://developers.google.com/chart/interactive/docs/querylanguage#Label for more specifics on the QUERY function.

It's a query in google query language. A:E defines all the relevant data, SELECT defines which columns you want, AVG(C) takes the average of column C, the WHERE clause filters down which rows you want to look at, and finally the LABEL clause at the end makes it so that the returned columns have no headers. The full range of clauses is shown in the link

Yep, dates are super weird for reasons I don't understand.

I found more info here https://developers.google.com/chart/interactive/docs/querylanguage#Format

yeah, the Sheets ones are decent. Its surprising that this doesn't mention anything about skipping.

I couldn't find it mentioned anywhere else. I figured you had some sort of secret Sheets Ouija board :)

Check out the QUERY() function and underlying language. It is incredibly powerful.

As for ctrl+shift+end, there is no analogous option in Sheets. It's simply ctrl+shift+hold down until you get to the end. ctrl+space highlights the entire column, but not from a given cell.

I'm afraid not. You need to click Add for each of them.

Consider using QUERY() to build your own pivot tables. See the syntax for pivot, but keep in mind that it is not the easiest formula around.

I don't see it in the document you linked.

But in any case, if you are using QUERY you can use the limit clause and order by DESC clause to get the last 9 visits.

Oh, you are right. I calculated the sum instead.

You need to make a small change to the formula in B2. Guess what it is?

The documentation for google queries might help you solve this riddle. :D

&#x200B;

&#x200B;

Share a anonymised mockup of your sheet, If you want exact formulas.

The official docs are more than enough. What I want you to do is practice those functions,So that you will get a understanding on how to proceed. For ex, https://support.google.com/docs/answer/3093343 gives "conceptual application" in the example: How to group "Eng,Marketing and Sales" dept. salaries(3rd Sheet). Look into it. That's exactly what you want.

For more reference/syntax,search this page: https://developers.google.com/chart/interactive/docs/querylanguage

I think u/RemcoE33 suggestion is good. To use Google Drive Sync if you are looking for a slightly more customized solution an the sync option does not work for you, then if you're on a Windows machine with OneDrive you can have Power Automate / Flow update the file on your drive.

Microsoft flow can strip out attachments from emails with certain subjects and save them in a google drive folder.

https://www.google.com/url?sa=t&amp;source=web&amp;rct=j&amp;url=https://flow.microsoft.com/en-us/galleries/public/templates/14cac1c200814387986d11343b292258/save-gmail-attachments-to-your-google-drive/&amp;ved=2ahUKEwiI3IX5w6XhAhWXSxUIHW4TCP0QFjA...

Maybe the start of step one

You might want to see if there are add-ons you like.

https://workspace.google.com/marketplace/app/chartexpo_%E2%80%93_best_data_visualization_tool/643809879384

I haven't used this one, just using it as an example.

Received the first review (5⭐️ 🎉) for my what-if analysis add-on (Ponder). It only supported Goal Seek at the time, but that motivated me to add support for data tables. Just pushed out the update. Let me know what you think.

I recently discovered Goal Seek, but found that Google's implementation was painfully slow. So, I took it as a challenge to see if it could be improved. Here's my add-on. It will never be as fast as the same functionality on bare-metal Excel, but it's nearly to a 10x improvement from what's currently available. Let me know what you think!

I've no experience with them but there are a couple that look suitable at https://workspace.google.com/marketplace/search/solver?hl=en-GB&amp;pann=cwsdp

Use Zapier. You can scrape the email using their "Parser" tool and then have Zapier write the data to a row on Google Sheets:

https://zapier.com/help/email-parser/

Using the app?

1) https://www.google.com/sheets/about/
2) Request Desktop site.
3) Copy link & paste URL.

Now you can go to the desktop version. I have to do this all the time on my iPhone when there is a feature the app is missing (like editing graphs).

For links, either find it via Insert sub-menu, or using Command+k.

I just want to mention that you can use Google Keep app for this.

&#x200B;

That said, checkboxes are simply values true/false so:

1. Select the column with the groceries names (I guess it's column B)
2. Go to Format > Conditional Formatting
3. In the panel to the right, select format rules: Custom formula is...
4. The value of the formula is =A1=true
5. Where the formatting style, select background color > none
6. Click the ~~S~~ icon for strikethrough
7. Click Done

Hey _Damien_X!, I just went through something similar to this so I'm happy to help jump start you and maybe stir up a little discussion. I don't have the exact solution as my needs are a little different (I'm trying to generate part lists of the items leaving our warehouse). This method uses a google forms to generate a "Pick Ticket". This was designed for many users of all skill types. Its a work in progress.

If anyone has any leads in this direction it would be really helpful!

But here is what I did do:

First you can have google sheets auto create a QR code based on the data in the cell next to it (like the item name)

I used this: =IMAGE("https://api.qrserver.com/v1/create-qr-code/?size=150x150&amp;data="&amp;A1)

Then I use an app called Scan to Web by berrywing which is the app that activates your camera to scan a qr code as a method of input.

I used a Google Form to limit the mistakes.

Question 1. What Job are you working on?

Question 2. What item are you taking?

Question 3. How many?

Question 4. Next item are you taking?

Question 5. How many?

etc...

This generates a single sheet with everyone's materials pulled and for what job.

The hard part is getting employees to ACTUALLY do it!

And I have yet to find a way to decrease the inventory number in my "Inventory" sheet. In fact I cant find anyway to have it interact at all with a specific cell in my existing Inventory sheet, as I would like it to adjust the inventory number as well.

I hope this helps!