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

From 3.5 billion Reddit comments

The format for dates is date '2019-06-25' so you need to convert your values to that format as a string before passing it to a query.

You can't add operators inside the query itself. The query language is limited to what is documented here: https://developers.google.com/chart/interactive/docs/querylanguage

>i've tried using now()+30 but that doesn't work, either.

if you want a query that uses the current date, convert the current date to the format above with TEXT(NOW(),"YYYY-MM-DD") and remember that concatenating strings is done with the symbol &, so instead of this...

=QUERY(C:C,"select count(C) where C < ( today() + 30 ) label count(C) ''",1)

you would do this:

=QUERY(C:C,"select count(C) where C < date '"& TEXT(today() + 30,"YYYY-MM-DD") &"' label count(C) ''",1)

This way, the actual text that is passed as the 2nd parameter to the function query is:

select count(C) where C < date '2019-06-25' label count(C) ''

and that would be a valid query

Yes, simply add B to the "select" part and you get 2 columns:

=QUERY(A2:B39,"select A,B order by B desc limit 3")

If you are going to use queries rather than array formulas, I'd advise to check the documentation here. They are very powerful but not so intuitive sometimes.

Here is an idea for it. You'd only have to write a script to replace B1:F1 with the names of the 5 most recent worksheets.

Edit: The new "Format" for daily data is flexible. As long as you label each row with the exercise in column A, the total/snitt/max calculations should work.

https://docs.google.com/spreadsheets/d/1_0mMOoa2wZ9kj4bNIxlaUYIaQWILgPRO30PTYnpoCvA/edit?usp=sharing

I haven't worked much with charts so I don't know how to deal with line graphs missing data points. Also, I'm not sure if you have to trigger an update to the charts or not. The fields they are referencing will have the updated data in them from just updating the cells B1:F1, but it may not redraw the chart automatically.

Edit: If you absolutely never break from your routine, you might be able to use the 6 latest worksheets and reference only evens/odds for the exercises you are doing on even/odd days. That might resolve the line graph issue.

Ex. One set of graphs for even day exercises, one set of graphs for odd day exercises.

Also, there is interpolateNulls referenced here:

https://developers.google.com/chart/interactive/docs/gallery/linechart

I have used this tool about 15,000+ (seriously) https://workspace.google.com/marketplace/app/form_publisher_form_to_pdf_google_docs_t/827172627657

One very easy option would be to access the sheet in json format, then you can whatever you like with whatever data cells may contain.

The sheet would have to be public though, so it depends on what this cell (or cells?) contain. If it were just a number of something rather anonymous this might be okay for you.

i.e. https://www.freecodecamp.org/news/cjn-google-sheets-as-json-endpoint/

Otherwise I think you'd be looking at the API's, but as you reference using HTML I figure you aren't confident in a lot of backend scripts that might make used of the API.

Ok, so I kind of went a little nuts with this. I added a tab called from script. I created a script that generates a list of the data in the format you want it. I added a menu item in the script drop down that appends the list to the tab I added.

Here is the code

Reading this post reminded me that it also mentions these "back-quotes" or grave accents in the Query Language Reference. I think another common use of these back-quotes is when you are selecting columns with spaces in their header names.

What you're trying to do can be accomplished without the Array and without the CountA. You just need the Query function (e.g., =QUERY(<data>, SELECT COUNT(A) WHERE <arguments>). Read up on syntax here.

The reason your getting an unexpected count is that your query is returning a header row. Because you've wrapped it in CountA, that's returning a non-zero. Use a header argument in your query to eliminate this.

well, not really SQL but it supports some basic features, yes :)

see https://developers.google.com/chart/interactive/docs/querylanguage

Take a look here to implement sorting the query: https://developers.google.com/chart/interactive/docs/querylanguage

Can you explain to me what the array formula is supposed to do, break it down for me?

=ArrayFormula(unique(query(if({1,0},countif(if('Form Responses'!G2:G<>"",'Form Responses'!G2:G), 'Form Responses'!G2:G),'Form Responses'!G2:G),"select Col2 where Col1="&max(countif(if('Form Responses'!G2:G<>"",'Form Responses'!G2:G),'Form Responses'!G2:G),'Form Responses'!G2:G)&" ")))

Edit: This seems to fix the problem, there was an issue with capitalisation:

=ArrayFormula(proper(unique(query(if({1,0},countif(if(lower('Form Responses'!G2:G)<>"",lower('Form Responses'!G2:G)), lower('Form Responses'!G2:G)),lower('Form Responses'!G2:G)),"select Col2 where Col1="&max(countif(if(lower('Form Responses'!G2:G)<>"",lower('Form Responses'!G2:G)),lower('Form Responses'!G2:G)),lower('Form Responses'!G2:G))&" "))))

I struggled with query()s at first but the Google documentation (https://developers.google.com/chart/interactive/docs/querylanguage) is great and querys tend to provide some relatively verbose solutions. One trick I found for standardising query functions is to do a simple filter and/or sort on the input data so that the function accepts it as an array instead of referencing columns (Col1 vs ColA).

I don't think those sheets functions will work as often websites have the data served dynamically. You should invest a little time and use python to scrape the sites you are interested in. Check out Beautiful Soup for grabbing data from HTML. This is a good tutorial to help you get started. Might seem intimidating at first but it's not that tricky, promise.

Not exactly what you were looking for, but you can build something like this pretty easily using Coda (the company I work for). Here's an example I threw together in a few minutes:

https://coda.io/@eric-koleda/fantasy-results

Coda is really good at working with tables of structured data and creating UIs that let you filter it. Enjoy!

Use a Google Form to collect the scores. You could even use the FormRanger add-on to keep a drop down list populated with agent names / IDs.

There's a ton of them out there that will do it in Sheets: https://workspace.google.com/marketplace/search/mail%20merge

When you say "Outlook" do you mean from an at outlook dot com email address (sorry if that's a stupid question)? I think most of them on that list will send it via your GMail / G Suite account. I run Mailman (open source) and we just have Gmail / G Suite but we could certainly add other email providers to it.

How many people enter a 5k Fire Department race? I mean like what are you going to do if 10 people all cross together?

What you ask is doable but I wonder if a better solution has already been created, say as an app in the app store?

e.g

https://play.google.com/store/apps/details?id=com.quadddd.racewatch&amp;hl=en&amp;gl=US

It is JavaScript. So you need Apps script methods to interact with Gmail/ docs / sheets / calender etc .. but you need JavaScript methods to do all the other operations like filtering / move data. Good understanding of arrays (and 2d arrays for sheets.) and objects will help you a lot. I recommend to do this free JavaScript interactieve course.

Apps script is basically JavaScript. So getting data and writing data to the sheet are google apps script methods build on JavaScript. But when you have the data and you want to manipulate/filter/sum/if-else etc.. then this are all pure JavaScript methods.

freecodecamp has a great free JS course. More apps script related this YouTube channel is great.

Hi, here is wrote some comments for you. Apps script is javascript. Yes you need google sheets methods for ineracting with the sheet but SWITCH, ARRAYS, OBJECTS, IF, OPERATORS ect.. is just javascript. Here is a good free course.

If you share a mock sheet (with edit rights) with your setup i can help you out from there.

There are quite a few "mail merge" type apps that work with sheets. Here are two:

• Document Studio

• Mail merge for Gmail - Yet Another Mail Merge - YAMM

If this ID is found in either in the URL on the video's web page it should be possible but you need to first learn about the Sheets importxml function and the basics of Regular Expressions. Here's an article to get you started:

https://zapier.com/blog/google-sheets-importxml-guide/

https://zapier.com/blog/google-sheets-importxml-guide/

I've used this app for mail merges. It can send emails with Drive attachments on a schedule:

https://yamm.com

Here's another one. I haven't used it myself but the guy who made it does a lot of Sheets tutorials and is probably legit.:

https://workspace.google.com/marketplace/app/mail_merge_with_attachments/223404411203

I've been using this add-on for years: https://workspace.google.com/marketplace/app/arraythis/1098041258001 - It used to have a logo so I don't know if it's actively supported any more, but it's working for me at the moment 🤷‍♀️

There are services that does this. Another one

Zapier has an SMS connector.

https://zapier.com/apps/sms/integrations

I imagine SMS data rates apply, but probably much cheaper than a $50 / month subscription to some other service.

Install this add-on:

https://workspace.google.com/marketplace/app/numbertext/505819167361

And use the formula:

=MONEYTEXT(A1,"ZAR","en")

It will output:

One thousand two hundred South African rand and ninety-five cents

In case you don't want it to say "South African" in the middle you can use the following formula:

=TRIM(SUBSTITUTE(MONEYTEXT(A1,"ZAR","en"),"South African",""))

And at last, if you want the . dot at the end you can add

&"."

to the very end of the previous formula.

&#x200B;

Hope this helps! :)

So you're trying to access/trigger the Goal Seek add-on from within your own macro? That isn't possible for any add-ons.

If it's anything else then we're going to need to see the macro code.

I’m sure it’s possible, but another option would be to buy a tool like Zapier, which finishes this task in literal minutes, and is less fragile.

https://zapier.com/apps/google-sheets/integrations/marketo

Back in my day...

https://www.amazon.com/Accounting-Ledger-Book-Bookkeeping-Business/dp/B08GDKG9Z1

But while you're here do tell us more about your trip

CSE Search - this extension used to work when it was new but it hasn't been updated since 2019 and the listing has no icon anymore so YMMV. Could still be worth a shot though

There are some free Google Sheets add-ons that could help. I use the Coefficient Add-on (disclaimer, I also do some contract work for them) which could be used in your case to automatically pull data from one spreadsheet to another spreadsheet, or from BigQuery to a spreadsheet. You can filter the data at the same time - so if you have a master spreadsheet, you could schedule a Coefficient job to run every hour to basically copy all data from the master spreadsheet (optionally filtered on only data for each specific client) and paste it into each of your clients' spreadsheets.

Would that work?

Connect google sheets to Google BigQuery and Metabase has a native connection to BigQuery.

happy to help! To learn QUERY() I recommend you to watch this playlist. The official documentation also does a good job at explaining it.

I ran into this problem a lot in my previous job and the only solution I found was to not use formula fields on SFDC reports and instead calculate the formulas in a column of Google Sheets.

I eventually switched to Coefficient's Add-on because I found Data Connector too buggy. (And disclosure, I liked their product so much that I ended up joining the team at Coefficient)

HubSpot API can be notoriously difficult to work with. I recently joined the team behind the Coefficient Add-on because I was so impressed with how easy their product makes data imports from HubSpot and other data sources. It's free to use if you don't need fully-automated data imports.

You can install an automation add-ons from the google workspace marketplace like this:

https://workspace.google.com/marketplace/app/sheet_automation_email_form_notification/250108887537

Otherwise you need to create your own script.

Without MYKEY we can't see how the returned json data is structured so it's incredibly hard to help you. You could try logging the data to a Google Sheet with a service like IFTTT: https://ifttt.com/weather and then using QUERY()s or FILTER()s to refine the logged data.

The group by clause of the QUERY does that. If you want to learn more about it I recommend you this playlist or Google's official documentation.

No problem. Here's the docs for the query language https://developers.google.com/chart/interactive/docs/querylanguage

Let me know if you have any trouble or want to share a copy of the sheet for help.

If that solves your problem for now reply "solution verified" to mark the thread solved.

the short answer is no. Not without a lot of coding expertise.

scraping from websites is tedious, requires a lot of trial and error and involves being very conversant in html.

Also, most consumer facing websites go to great lengths to prevent scraping because it undermines the purpose of the website which is to get you to see ads or buy things from them.

You might take a look at ParseHub. I've used them for something in the past. I found it pretty convoluted, but was eventually able to cobble something together.

https://www.parsehub.com/

The problem is the activate action you make. This is not nessery for the thing you want to be done. This should do. See that i made the script simpler. Info about the diffrence between var / let / const

&#x200B;

function FindUserAndMoveData() {

const userEmail = Session.getEffectiveUser().getEmail(); const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheetByName('Data'); sheet.getRange('G2').setValue(userEmail); const sourceData = spreadsheet.getRange('A2:G2').getValues();

const target = SpreadsheetApp.openById("ABC...."); const target_sheet = target.getSheetByName("AllData"); const last_row = target_sheet.getLastRow();

target_sheet.getRange(last_row + 1, 1, sourceData.length, sourceData[0].length).setValues(sourceData); }

You've written a single straight function of ~580 lines. You need to refactor. This and this might be helpful. Basically, split up duplicated parts into separate functions: then call those functions from your main function with variables. Local IDEs(like vscode) make this easier(Use @google/clasp to connect the editor to your local env, if you haven't already).

Okay I have managed to find a different website, and I managed to scrape the bid value! Now what exactly should I change in this formula to make it reference a cell, instead of typing the ticker manually?

Screenshot

I did insert the link. I can make the hyperlink with the hyperlink function as well. However I tried this, but it didn't work: =IMPORTXML(REGEXEXTRACT(FORMULATEXT(G8);"""(.+?)"""); "//*[@id='meta-left-col']/div[2]/span[2]")")"

Screenshot included for more context. Thanks for the answer anyways!

Screenshot

> but is there a way around it without moving my other sheets around?

What do you mean "moving my other sheets around"?

> is there a way to prevent it from adding product() above the column

Yes, with a label clause:

=QUERY({'Sheet 3'!C5:C1000,'Sheet3'!CF5:CF1000,'Sheet 2'!B3:B998},"Select Col1, Col2*Col3 where Col2>0 label Col2*Col3 ''",0)

gstatic.com isn't malware. It's Google's static CDN. See the Chrome privacy whitepaper for details. The simple answer is that Chrome uses this to detect internet connections that require login (like hotel networks and airplane wifi).

I have seen a lot of noise about malware sites using it as a redirect but I would be very very surprised if that was the case.

To be safe, take a look at the Chrome support page for removing malware.

The first thing that pops in my mind is conditional formatting.

Check this link out and look through the "Conditional formatting with text" section.

https://zapier.com/blog/conditional-formatting-google-sheets/

Hope this helps/sparks something.

Good luck! :)

Check this basic example: https://zapier.com/apps/google-sheets/integrations/sql-server/3583/add-new-sql-server-rows-to-a-google-sheets-spreadsheet

So do you want a more accurate price cause similar to rurbaniak14,

=IMPORTXML("https://coinranking.com/coin/7gHjBh7YK+safemoon-safemoon","//*[@id='__layout']/div/div[3]/section/div[4]/div/div/table/tbody/tr/td[1]/div/text()")

also returns the live price but only to two significant figures

jaysargotra, any ideas? )

I tried other sites, some of them with more luck. For example, tradingview.com seems to be more newby-dev friendly on the "industry" info ) Downside to that approach is that I do not know which market the ticket belongs to and with tradingview it is necessary to additionally specify the exchange

I'd rather stick to yahoo finance, but formula keeps returning N/A results

For the information they have previously entered, do you mean on the previous page or on previous submissions?

You can only be notified of Google Form submissions via email. Have you considered filtering your emails?

The form cannot be condensed for printing.

From what I can tell, you could consider Jotform as an alternative for your needs.

I think Appsheet works out for you!

Try Appsheets ...or since I've done this project for other prospects see a demo of it. The QR- code-generating one is in on another app. where they recieve it & they just come & scan in.

this is from BetterSheets. Oh gosh I hope he doesn't mind I publically posted this. He has a bunch of fantastic Google Sheet Tips for $49. Hopefully this script will help for this case though!

I dont really understand this. Can you explain further?
Video of my problem: https://www.loom.com/share/bf8e6e8877f7470088919cbf02d97e8f

I cant find an app for windows 10 google sheets? This is the problem if it helps:
https://www.loom.com/share/bf8e6e8877f7470088919cbf02d97e8f

This was a bit of a mission. Had a couple of us in there trying to work it out. The problem was that the main dictionaries like Oxford, Merriam, etc., all have terrible formatting and put everything in one section.

PowerThesaurus keeps it nice and neat. Here's my formula:

=iferror(index(IMPORTXML("https://www.powerthesaurus.org/"&amp;trim(A2)&amp;"/definitions/","//div[@class='lj_gh']"),1),)

Here is a copy of your sheet.

1. Created an query tab to filter out the columns and >= 80%
2. Created a custom menu: ExportCSV
3. Created a script to export this as comma ( , ) CSV file and insert this to a specific folder on your drive
   1. Tools -> Script editor
   2. Change the folder id on codeline 10, with is found in the url.
   3. Save an close, run the script via custom menu -> Give permission.

Hope this is what you are after.

There is no "Insert image in cell" method for apps scripts (yet). But you could use the =image formula.

1. Create a folder on your google drive
2. upload the images to that folder
3. Go to your spreadsheet -> Tools -> Script editor
4. Clear the little code you see
5. Paste the code from below
6. Change:
   1. Sheetname (line 4)
   2. Drive folder id (line 6)
   3. The 1 and 2 (line 16) to the startrow and column number.
7. Inside the script editor go to: Recourses -> Advanced google services
   1. Enable: Drive API
8. Next to the little play button you have a dropdown, choose: image and hit play.
   1. Give permission.

Now you should have all the images inside your sheet.

&#x200B;

function image() { const ss = SpreadsheetApp.getActiveSpreadsheet(); //Change Data to the sheetname const sheet = ss.getSheetByName("Data"); //Change the folder id where the images are const imageFolderID = "1--mrZWXlTZxuAwZysPNxD5VWWjh8"; const files = DriveApp.getFolderById(imageFolderID).getFiles(); const output = [];

while(files.hasNext()){ let file = files.next(); output.push(['=IMAGE("' + getThumbNailLink(file.getId()) + '",1)']); }

//Change 1 (row) to the desire startrow, change 2 (column) to desire column. So below is B1. sheet.getRange(1, 2, output.length).setValues(output);
}

function getThumbNailLink(fileId) { const file = Drive.Files.get(fileId); return file.thumbnailLink; }

That's weird, it should be accessible...Did you add everything here between the quotes, no spaces: "M1lugvAXKKtUxn_vdAG9JZleS6DrsjUUV"

Do you have full ownership of the spreadsheet and Google Apps Script page?

Can you access the library here: https://script.google.com/d/1Mc8BthYthXx6CoIz90-JiSzSafVnT6U3t0z_W3hLTAX5ek4w0G_EIrNw/edit?usp=drive_web

I would not go down the publish as an add-on route. A script associated with a worksheet is essentially just a Google project contained in the sheet it is associated with. You can go to script.Google.com and create a project, put the code in it and then either use the google drive api or as TobofCob suggest parameterize it and pass in the name (or object IDs).

>//Get the row & column indexes of the edited cell
var row = e.range.getRow();
var col = e.range.getColumn();

Hi,

I tried it in the sheet and it does not hide the cells. I then got an email that said:

Your script, para script test, has recently failed to finish successfully. A summary of the failure(s) is shown below. To configure the triggers for this script, or change your setting for receiving future failure notifications, click here.

The script is used by the document script test.

TypeError: Cannot read property 'getRow' of undefined.

I have double-checked that the range is assigned properly.

I am the owner. I've authorized/ran a lot of scripts, so I'm familiar with what your referring to. But this is my first time attempting it from a macro and then setting up a trigger.

Although I am not 100% positive, I'm pretty sure I got that authorization request dialog box the first time I set up the macro. If not, then no, I did not get prompted to authorize it. The script IS listed in my list of scripts at the script.google.com dashboard, along with my other projects.

How about this web app? It's powered by apps script.

https://script.google.com/macros/s/AKfycby9mZ8fB211_6_ohlmgWAM5UZ9sUjeFGWPt_HL1wVWuZtnbE-lB/exec

Hi yes. please. i am not sure if you can delete them. please try it here, you have to ask the owner to delete them if you can't., I'm dad.

He updated the description to include a link to the code.

&#x200B;

https://script.google.com/d/1A0gU0agr_gs9Jdf8BykLk2C_WP3U40P3Kawewt_O2ePXrHSvlk1tK2q8/edit?usp=sharing

>Try Parcel API Then you can fetch this information with apps script.

I'm pretty sure this is for the ParcelJS bundler (an NPM package that optimizes resources for distribution, usually for websites), not for tracking packages. Their API is for programmatic access like in an NPM script instead of calling the parcel CLI.

Your second suggestion seems on-point! Though IMO these delivery-tracking sites likely load information over AJAX, so you'll probably need to reverse-engineer that request to get at delivery status.

No the where clause belongs into your query

QUERY('All-Time Leaderboard'!B2:F, "Select B,C,F where D = '"&D4&"' limit 10")

Check also the documentation of the query language for more examples

https://developers.google.com/chart/interactive/docs/querylanguage

Do you have the frequency count in a column? Let's say something like:

Column A: text column B: frequency count?

Then I would use query:

Select A, B order by B des limit 1 offset 1

(For the second rank)

Select A, B order by B des limit 1 offset 2

(For the third rank)

Check out the docs for query:

https://developers.google.com/chart/interactive/docs/querylanguage

(I am on my mobile so couldn't check your sheet)

In this case it's best to use query. With that you can query your dataset like

Select A,B,C,D where B == Attributname etc.

That also works in combination with IMPORTRANGE to get the data into the other sheet.

Check out the docs here:

https://developers.google.com/chart/interactive/docs/querylanguage

And how do you determine the 100 you need? What I recommend you is to read the list with a query:

"Select C where C <> "" limit 100"

And now we need to know how you determine the most recents? Is it the last 100 days or the last 100 hours etc

This clause could then be added to the query above

Something like: Select C where D > 99 and D < 201

In this case the marker would be in column D and would be an incrementing number. As you see you have a lot of flexibility

Anyway this way you just add rows to your table, the list gets longer and you just select what you need. You don't need a script and don't have to delete anything.

You can make also an condition which checks the value of a cell so you can react to user input.

Check out the documentation for the query language:

https://developers.google.com/chart/interactive/docs/querylanguage

I found this:

&nbsp;

https://developers.google.com/chart/interactive/docs/querylanguage#operators

&nbsp;

Go to arithmetic operators

&nbsp;

Query only recognizes +, -, / and * as operators.

Unfortunatly, I don't believe it's possible using QUERY function. Here's an exerpt from the documentation about QUERY:

Runs a Google Visualization API Query Language query across data.

Sample Usage

QUERY(A2:E6,"select avg(A) pivot B")

QUERY(A2:E6,F2,FALSE)

Syntax

QUERY(data, query, [headers])

• data
  - The range of cells to perform the query on.
  

Each column of data
can only hold boolean, numeric (including date/time types) or string values.

• In case of mixed data types in a single column, the majority data type determines the data type of the column for query purposes. Minority data types are considered null values.

• query
  - The query to perform, written in the Google Visualization API Query Language.

• The value for query
  must either be enclosed in quotation marks or be a reference to a cell containing the appropriate text.

&#x200B;

As you can see, Query only takes strings, Booleans, or numbers. So it won't take hyperlink values.

I would suggest you try using something like "IMPORTRANGE", "FILTER" or similar functions to sort and filter the data you want to use if you want to keep the hyper link. Or simply remove the hyperlink and store the link as plain text.

BY need to be back-quoted because it's a reserved word.

https://developers.google.com/chart/interactive/docs/querylanguage#reserved-words

So you query should look like

=QUERY ('Master Master List' ! A2:BY, "SELECT A WHERE BY = 'YES'"

you could also wrap an element of your select statement in lower(). =query(A:A, "select lower(A)") is a valid query, for example.

The following link has a table of the functions you can use inside a query.

https://developers.google.com/chart/interactive/docs/querylanguage#scalar-functions

As you can see in the language reference, there is no where...in clause in google query. Maybe you remember it from using SQL.

If you just want to count values, try something like this:

=COUNTIF(FILTER(Data!B:B,MATCH(Data!A:A,Lookup!A:A,0)),"apple")

The query syntax doesn't allow operations inside the query. You'll need the build that column of 1s and 0s outside of the query.

You can do it as a new column in that named range. For example in AZ2: =ARRAYFORMULA(IF(G2:G>35,1,0))}

=QUERY(history, "select MAX(C), MAX(D), COUNT(G), SUM(AZ) where AE=1 AND A=0 group by AF order by MAX(C)", 1)

You could also build it as an array in the first parameter, but then your query needs to use column numbers instead of letters:

=QUERY({history!A:G, history!AE:AF, ARRAYFORMULA(IF(history!G:G>35,1,0))}, "select MAX(Col3), MAX(Col4), COUNT(Col7),SUM(Col10) where Col8=1 AND Col1=0 group by Col9 order by MAX(Col3)", 1)

(Assuming that "history" is also the name of the sheet)

Read the comment thread for the solution [here](/r/googlesheets/comments/bod0l7/highlight_2nd_and_3rd_place_quantity_logs/eni24ks/)

> Yes, simply add B to the "select" part and you get 2 columns: > > =QUERY(A2:B39,"select A,B order by B desc limit 3") > >If you are going to use queries rather than array formulas, I'd advise to check the documentation here. They are very powerful but not so intuitive sometimes.

If you have used filters in the pivot tables, they will not refresh automatically. You need to clear and apply the filter every time.

As a solution you would need to build the pivot tables with a QUERY() formula that uses the pivot clause. Those are a bit hard to create and might need some trial and error if you never used QUERY():

https://developers.google.com/chart/interactive/docs/querylanguage#pivot

Thanks!
BTW, is there an official documentation that mentions this feature? I did not find anything on support.google.com/docs nor developers.google.com/chart/interactive/docs/querylanguage.

You can do whatever filtering that the query function allows. Here's a not-terrible guide to how to format different query commands. What I've learned so far is that when using query on an array, the array sections should be the same number of columns in width, and you should use "Col1" "Col2" etc to refer to the columns rather than by the column's alpha designation ("A, B, C,", etc.) or by its headers. In my query, I'm filtering out all rows that don't have a value of 1 or greater in a specific column, and then sorting the results by the numerical value of the result in that column.

EDIT: Whoops, forgot the link to the query language guide: https://developers.google.com/chart/interactive/docs/querylanguage

Is the rest of your team using Microsoft (Outlook etc.) or are they all in Google? This sounds like it should be a mini application. Unfortunately Google App Maker is almost gone, but they have a paid service called App Sheet.

If your team uses Microsoft then Power Apps is specifically built for this kind of stuff (I always think of it as a system designed to approve expense reports).

Building this in Google Sheets would require quite a bit of Scripting to get it to work more than is normally sufficient for a solution.

There is a java library that you could look into

https://momentjs.com/timezone/

I'm sorry I don't have time now to check it out. But I have used it in the past and it was working fine. I'll hunt out the project and I was working on and see if I can resurrect it.

Check out Supermetrics or Sheetgo, they both have large, successful businesses built around their Sheets add-ons. Or on a smaller scale, I just saw this post today about the Sheet2Site add-on reaching $10K/month: https://www.indiehackers.com/post/what-you-can-learn-from-andrey-azimov-getting-to-10k-m-f0c6d6366d

>is it possible to do this without getting a whatsapp client / API

No. You'll need to connect to the WhatsApp API or through one of their partners who may provide an API themselves

If you are willing to use a paid solution, I suggest a little software I use, is called "Docparser" it can extract data from PDF files, and upload it straight into a spreadsheet of your choosing, is not expensive, is very reasonable,you get charge per month and you choose your own monthly plan, starts at $40 a month, you can upload the pdf files to a Dropbox folder, and the program can automatically extract the data from whatever PDF files you have uploaded to the specified dropbox folder.

check it out: https://docparser.com/

I use Alfred on mac. So if i type !tel i get my phone number. And #func will create a boilerplate apps script function. On windows there is textexpander.

It's a pretty lengthy JSON. You'll need JMESPath or some other filter to get what you want efficiently. I run the Data Connector Add-on (it's open source). You can put in the URL that /u/cldellow mentioned, then apply a JMESPath filter you want; for instance eventGroup.name will return MLB but you should be able to get whatever data you need with it.

Sounds awesome.

For Add-Ons, I've found "Copy Down" to be indispensable when people are submitting forms, but I want the formatting to look the same for the new row or to copy down formulas to the new row. https://workspace.google.com/u/0/marketplace/app/copy_down/889269636541?hl=en&amp;pann=sheets_addon_widget

For those looking, the GPT3() function is available through an add-on called Overfit.ai: https://workspace.google.com/marketplace/app/overfitai/308627233175. You need an OpenAI API key to use it.

For more info on the API, see here: https://openai.com/blog/openai-api/

As for how this add-on implements GPT3, I imagine you won't be contributing to teaching the AI directly, especially with how small the input is. It looks like it's basically a search engine.

This is a native feature of Excel (scenarios). It's not supported in gSheets, though there are a few tools available which give you this kind of feature.

&#x200B;

For example - https://workspace.google.com/marketplace/app/whatif/430660745752?pann=cwsdp&amp;hl=en

Not saying it’s what your looking for but I found Zapier to be great to pull data from sheets and pop it into just about anything or even another sheet limited coding required simple GUI

https://zapier.com/apps/google-sheets/integrations

It makes more sense to go the other way, Calendar to Sheets.

https://zapier.com/zapbook/google-calendar/google-sheets/

https://www.cloudbakers.com/blog/export-google-calendar-entries-to-a-google-spreadsheet

You need a generic usb bar code scanner, they are cheap on Amazon

Move the cursor to the cell, and scan - done