Күн бұрын
14 күн бұрын
14 күн бұрын
3 ай бұрын
4 ай бұрын
Пікірлер
@ass-q2v 2 күн бұрын
THANK YOU!
@jennykkk 6 күн бұрын
thank you thank you
@coupleracademy 5 күн бұрын
You are welcome!
@jigarsagar4450 7 күн бұрын
Hey hello, how can I use this formula with GOOGLEFINANCE? I tried using it once but it didn't yield the desired effect/result. Is there anyway I can work around current limitations?
@markuswinter-cdps3008 9 күн бұрын
Is it possible to use the query method dynamically whereby a column houses the imported URLs of all the sheets to be queried, rather than individually cutting and pasting each ID/URL into the formula? Thanks.
@coupleracademy 8 күн бұрын
Hello! If I understand you right, you need to follow these steps: 1) List the URLs or IDs of the sheets you want to query in a separate column 2) create a formula with INDIRECT 3) construct the QUERY formula In other words, to query a specific range in a sheet dynamically, you'll need to combine QUERY with INDIRECT. Here’s a simplified example: =QUERY(INDIRECT("'" & A2 & "'!A1:B10"), "SELECT A, B WHERE B > 10")
@markuswinter-cdps3008 8 күн бұрын
@@coupleracademy Thanks so much for the reply!!! Maybe if I give a bit more info, it could clarify my situation. So, my situation is such that I have a running list of reports for a calendar year. By the end of the year, there are 400 or so reports. Within the reports, there is an equipment2 sheet that I am trying to query with a range from B4:E28, where the results can be either 1 row, or all 25, depending on how much equipment was used for that given incident (potentially no rows, if no equipment was used). I have a script that imports all the URLs from the reports into a tab/sheet that is called ReportURLs, in a master tracking worksheet. Column D houses the URLs. I'm looking to query ReportURLs!D2:D dynamically to pull out all the data from each of the equipment2 tabs/sheets in each of the reports. Will your suggested Query/Indirect solution work for this? Thanks!
@coupleracademy 5 күн бұрын
@@markuswinter-cdps3008 To be honest, it's hard to provide you with a working solution this way but at least I'll try to :) So: 1) go to your ReportURLs sheet and in column E (next to your URLs in column D), enter the following formula in E2: =IF(D2="", "", IMPORTRANGE(D2, "equipment2!B4:E28")) 2) Create a new sheet, let's call it CombinedData and in cell A1 enter the following formula: =QUERY(ARRAYFORMULA(FLATTEN(ReportURLs!E2:E)), "SELECT * WHERE Col1 IS NOT NULL", 0)
@PritiShendre-l9s 9 күн бұрын
is there any way to pull our salesforce dashboard from salesforce to looker studio?
@coupleracademy 9 күн бұрын
Sure! Just create a free Coupler.io account and create a new importer with Salesforce as a data source and Looker Studio as a destination. The setup process is pretty intuitive, but you can book a demo with our specialist to guide you through l.rw.rw/book-a-demo-yt
@coupleracademy 10 күн бұрын
If you liked this video, check our other tutorials to supercharge your Google Sheets experience 🚀 🔵SUM, SUMIF, and SUMIFS functions kzbin.info/www/bejne/hquxmJ-ebZaBd68 🔵conditional formatting basics kzbin.info/www/bejne/hZaXm4yYob2qbs0 🔵ARRAYFORMULA basics kzbin.info/www/bejne/eoaZi6F_gJ5lra8 🔵data validation basics kzbin.info/www/bejne/e5LCeKVsis2ipNU
@WorkoutNerd3221 11 күн бұрын
I have permission to al of the files but It still doesn't work.
@coupleracademy 10 күн бұрын
Do you see any specific error message?
@WorkoutNerd3221 10 күн бұрын
@@coupleracademy no it says "you need permission, learn more" I am the owner of all of the files, but maybe because the google drive is managed by my employer?
@WorkoutNerd3221 10 күн бұрын
@@coupleracademy do you have an email or support number to help? Call Google, I guess?
@coupleracademy 9 күн бұрын
@@WorkoutNerd3221 Google typically does not offer live chat support for general inquiries or customer support issues. However, if you are a Google Workspace (formerly G Suite) customers, there is an option for live chat support available through the Google Admin Console.
@MitchellDurham-z7p 12 күн бұрын
I was just confused before I watched this vide
@coupleracademy 12 күн бұрын
Hope everything's clear now but if not, check out other Google Sheets tutorials on our channel, we explain different Google Sheets functions and functionalities in detail. Take care!
@MitchellDurham-z7p 12 күн бұрын
This was so helpful!
@coupleracademy 12 күн бұрын
thanks! :)
@mikeconza1698 12 күн бұрын
Very helpful - thank you for this
@coupleracademy 12 күн бұрын
You're very welcome!
@jhofastcopas 12 күн бұрын
Is it possible to connect in real time? I am experiencing a delay in refreshing the information.
@coupleracademy 12 күн бұрын
Hi! Real-time no but you can have data refreshed as often as every 15 minutes.
@mooripo 13 күн бұрын
This is so correct until I use Label BEFORE the end of the query like so : ( =QUERY(Invoices_Extracted_on_2024.07.21!A1:L,"SELECT Col"&XMATCH("INVOICE_ID",header)&", Col"&XMATCH("Date",header)&", Col"&XMATCH("Invoice#",header)&", Col"&XMATCH("Customer Name",header)&", Col"&XMATCH("Invoice Status",header)&", Col"&XMATCH("Due Date",header)&", Col"&XMATCH("Due Days",header)&", Col"&XMATCH("Sub Total",header)&", Col"&XMATCH("Invoice Amount",header)&" - Col"&XMATCH("Sub Total",header)&" LABEL Col"&XMATCH("Invoice Amount",header)&" - Col"&XMATCH("Sub Total",header)&" 'Discount', Col"&XMATCH("Invoice Amount",header)&", Col"&XMATCH("Balance",header)&", Col"&XMATCH("Adjustment",header)&", Col"&XMATCH("Created By",header)) ) Although, when I use Label at the END it works correctly like in this function : ( =QUERY(Invoices_Extracted_on_2024.07.21!A1:L,"SELECT Col"&XMATCH("INVOICE_ID",header)&", Col"&XMATCH("Date",header)&", Col"&XMATCH("Invoice#",header)&", Col"&XMATCH("Customer Name",header)&", Col"&XMATCH("Invoice Status",header)&", Col"&XMATCH("Due Date",header)&", Col"&XMATCH("Due Days",header)&", Col"&XMATCH("Sub Total",header)&", Col"&XMATCH("Invoice Amount",header)&" - Col"&XMATCH("Sub Total",header)&" LABEL Col"&XMATCH("Invoice Amount",header)&" - Col"&XMATCH("Sub Total",header)&" 'Discount'") ) Finishing both calculated column and leaving the labeling to the end doesn't work :/ ( =QUERY(Invoices_Extracted_on_2024.07.21!A1:L,"SELECT Col"&XMATCH("INVOICE_ID",header)&", Col"&XMATCH("Date",header)&", Col"&XMATCH("Invoice#",header)&", Col"&XMATCH("Customer Name",header)&", Col"&XMATCH("Invoice Status",header)&", Col"&XMATCH("Due Date",header)&", Col"&XMATCH("Due Days",header)&", Col"&XMATCH("Sub Total",header)&", Col"&XMATCH("Invoice Amount",header)&" - Col"&XMATCH("Sub Total",header)&", Col"&XMATCH("Invoice Amount",header)&", Col"&XMATCH("Balance",header)&" - Col"&XMATCH("Invoice Amount",header)&", Col"&XMATCH("Balance",header)&", Col"&XMATCH("Adjustment",header)&", Col"&XMATCH("Created By",header)&" LABEL Col"&XMATCH("Invoice Amount",header)&" - Col"&XMATCH("Sub Total",header)&" 'Discount' )") ) *WORKING Here* ( =QUERY(Invoices_Extracted_on_2024.07.21!A1:L,"SELECT Col"&XMATCH("INVOICE_ID",header)&", Col"&XMATCH("Date",header)&", Col"&XMATCH("Invoice#",header)&", Col"&XMATCH("Customer Name",header)&", Col"&XMATCH("Invoice Status",header)&", Col"&XMATCH("Due Date",header)&", Col"&XMATCH("Due Days",header)&", Col"&XMATCH("Sub Total",header)&", Col"&XMATCH("Invoice Amount",header)&" - Col"&XMATCH("Sub Total",header)&", Col"&XMATCH("Invoice Amount",header)&", Col"&XMATCH("Balance",header)&" - Col"&XMATCH("Invoice Amount",header)&", Col"&XMATCH("Balance",header)&", Col"&XMATCH("Adjustment",header)&", Col"&XMATCH("Created By",header)&" LABEL"&" Col"&XMATCH("Invoice Amount",header)&" - Col"&XMATCH("Sub Total",header)&"'Disciount Amount', Col"&XMATCH("Balance",header)&" - Col"&XMATCH("Invoice Amount",header)&"'Paid sum'") )
@coupleracademy 12 күн бұрын
Hi! It looks like there might be a syntax issue when using multiple LABEL statements with calculated columns. Ensure you have proper spacing and syntax. Here’s a corrected example: =QUERY(Invoices_Extracted_on_2024.07.21!A1:L, "SELECT Col"&XMATCH("INVOICE_ID",header)&", Col"&XMATCH("Date",header)&", Col"&XMATCH("Invoice#",header)&", Col"&XMATCH("Customer Name",header)&", Col"&XMATCH("Invoice Status",header)&", Col"&XMATCH("Due Date",header)&", Col"&XMATCH("Due Days",header)&", Col"&XMATCH("Sub Total",header)&", Col"&XMATCH("Invoice Amount",header)&" - Col"&XMATCH("Sub Total",header)&", Col"&XMATCH("Invoice Amount",header)&", Col"&XMATCH("Balance",header)&" - Col"&XMATCH("Invoice Amount",header)&", Col"&XMATCH("Balance",header)&", Col"&XMATCH("Adjustment",header)&", Col"&XMATCH("Created By",header)&" LABEL Col"&XMATCH("Invoice Amount",header)&" - Col"&XMATCH("Sub Total",header)&" 'Discount', Col"&XMATCH("Balance",header)&" - Col"&XMATCH("Invoice Amount",header)&" 'Paid sum'") The key is to ensure each LABEL is correctly associated with its column, and there’s proper spacing and punctuation.
@mooripo 13 күн бұрын
thanks
@coupleracademy 12 күн бұрын
You're welcome!
@tridibbiswas3361 14 күн бұрын
Thank you. This is the prefect video I was lookiing for when I am trying to migrte from excel and the query fuction is so much more versatile. Could also do video on IMPORTHTML
@coupleracademy 12 күн бұрын
Hi! Thank you so much and glad you enjoyed our video. We don't have anything on IMPORTHTML planned at the moment but we'll definitely note your request and will discuss it with the team. Thanks again!
@tridibbiswas3361 14 күн бұрын
Thank you for the video. Could you also do one with IMPORTHTML in Google sheets
@coupleracademy 12 күн бұрын
Thanks! Noted your request, we'll consider it when discussing future videos.
@halimagana6824 15 күн бұрын
This is a great video, but the terminologies are not for beginners, its too fast paced as well.
@coupleracademy 15 күн бұрын
Noted, thanks for your feedback, we'll aim to improve in the future videos.
@videomonkpvr 16 күн бұрын
Great way to combine header and formula into a single cell. Nice hack. Really appreciate it
@coupleracademy 15 күн бұрын
Yup, pretty neat! Thanks a lot :)
@SpanishTownPHIZone2 17 күн бұрын
Hi, Would you be able to help me extract data from one google sheet to another if I have more than one columns with the same name and value? However what I would need to extract would be the most recent data that could be in either columns.
@coupleracademy 16 күн бұрын
Hi! To do that, you can use the FILTER and SORT functions. Here’s a quick example: - Identify the columns: Use FILTER to get all relevant rows. - Sort by date: Use SORT to order by the date column, with the most recent first. - Extract the latest: Use INDEX to get the most recent entry. Example: =INDEX(SORT(FILTER(Sheet1!A:C, Sheet1!A:A = "YourValue"), 3, FALSE), 1, 1) This gets the most recent value from columns A to C where column A matches "YourValue" and sorts by the third column (date).
@TechWookie 17 күн бұрын
How do you put in spacer columns or a static NULL column?
@coupleracademy 16 күн бұрын
Hi! You can use ' ' (single quotes) for empty columns or NULL for null columns. For example: =QUERY(A:D, "SELECT A, '', B, NULL, C")
@luanbaviloni6714 18 күн бұрын
For anyone getting error when trying to use QUERY functions, replace the comma separator for the semicolon separator.
@coupleracademy 16 күн бұрын
Thanks for the helpful tip! Regional settings can cause issues with separators. Switching from commas to semicolons is a great solution for those experiencing errors in QUERY syntax.
@Mpumzar 23 күн бұрын
Nice one man, you earn one more subscribe 👍
@coupleracademy 23 күн бұрын
Welcome on board!
@AmnaZaim 25 күн бұрын
How can I make daily projections vs actual dashboard with filter view of funnel
@coupleracademy 24 күн бұрын
Thanks for your question! To create a daily projections vs actual dashboard with a filter view of the funnel: - Set up columns for dates, projections, and actual values. - Use SUMIF or QUERY formulas to aggregate daily data. - Create charts for projections vs actuals. - Add filter views via Data > Filter Views > Create New Filter View. - For a funnel chart, organize data by stages and use a bar chart.
@bandaid.7878 24 күн бұрын
How can I aggregate daily daily data? And also, what will be the best chart to use in this situation?
@coupleracademy 24 күн бұрын
For daily aggregations, it's best to use either SUMIF or QUERY formulas. Here some examples: =SUMIF(A:A, "2024-01-01", B:B) =QUERY(A:B, "SELECT A, SUM(B) GROUP BY A") And as for the chart, i think line chart will work perfectly fine for the job :)
@bandaid.7878 24 күн бұрын
@@coupleracademy thank you so much!!
@Malpriorvids 28 күн бұрын
As an engineer, I see that the glass is twice the size that it needs to be.
@Kikikuku2 28 күн бұрын
impatiently waiting!
@coupleracademy 26 күн бұрын
awesome to hear! :)
@louisadibe3189 Ай бұрын
I just began watching your video for the first time, any idea why my tool box section is not highlighted? Thanks
@coupleracademy Ай бұрын
Hi! If your toolbox section isn't highlighted, it might be due to a couple of reasons: - Edit Mode: Make sure you're in Edit mode. You can switch to Edit mode by clicking the "Edit" button at the top right of your screen. - Permissions: Ensure you have the necessary permissions to edit the report. If it's a shared report, you might need edit access. - Browser Issues: Sometimes, a quick refresh or trying a different browser can help resolve display issues.
@YuriIvanovAM Ай бұрын
Deje todas mis Recetas en el Area Necromancia Es la raya.
@coupleracademy Ай бұрын
Hey there! Thanks for dropping by! 😊 It looks like you might have left your recipes in the Necromancy Area. That's quite an interesting place! If you need any help with Looker Studio, feel free to ask. We're here to help with data magic, not just necromancy! 😄
@andyle3190 Ай бұрын
Can it work with an entire folder? I mean I link my master sheet to a folder. My colleague place their files into folder. And my master sheet automatically import data from all file (including new one) in the folder.
@coupleracademy Ай бұрын
Hi! While Google Sheets doesn't have a built-in feature to link directly to a folder and automatically import data from all files in that folder, you can achieve this using Google Apps Script. I came across the following code that should do the job but haven't tested it, check if it works for you: function importFromFolder() { var folder = DriveApp.getFolderById('YOUR_FOLDER_ID'); var files = folder.getFiles(); var masterSheet = SpreadsheetApp.openById('YOUR_MASTER_SHEET_ID').getActiveSheet(); while (files.hasNext()) { var file = files.next(); var sheet = SpreadsheetApp.open(file).getSheets()[0]; var data = sheet.getDataRange().getValues(); masterSheet.getRange(masterSheet.getLastRow() + 1, 1, data.length, data[0].length).setValues(data); } }
@andyle3190 Ай бұрын
@@coupleracademy Thank you
@IsmahaniHuda Ай бұрын
for the table chart, how can I display the amount for only 2 d.p?
@coupleracademy Ай бұрын
Hi! You can use the "Custom number format" option from the Format -> Number menu to decide on the number of decimal places.
@CaribouDataScience Ай бұрын
How do I format the Total Order column in the table?
@coupleracademy Ай бұрын
Hi! How would you like to format it? You'll find plenty of options in the 'Format' menu, such as changing the currency symbol, number of decimals, different alignment within cell, etc
@mohanadabdulrahim7202 Ай бұрын
This deserves more attention.. amazing video
@coupleracademy Ай бұрын
So happy to hear that. Thanks!
@rajeshmakwana-c2g Ай бұрын
super....what is correct syntax to get historical closing price for one particular day ?
@coupleracademy Ай бұрын
Hi! You'll want to use the following syntax: =GOOGLEFINANCE("ticker", "close", DATE(year, month, day)). For example: =GOOGLEFINANCE("AAPL", "close", DATE(2023, 3, 15)) If the stock market was closed on the chosen day, the function will return the close price for the next working day. If you have any other questions, feel free to ask :)
@jezzgrainger139 Ай бұрын
This is really great thank you! I'm hoping you may be able to help with a multiple function automation I'm working on, which I think would include ARRAYFORMULA as well as other functions, but it gets complicated for an idiot like me!! ;) I need to separate out an email address using "Surname"."Firstname"@---.---.country so that the output cell shows just the FIRSTNAME space SURNAME with letters in caps (PROPER function) I'm trying to use the SPLIT function, but there are multiple delimiters ("@" and "'.") and I only want the result to display in ONE CELL (SELECT COLUMN1) I reckon if you could do a video on extracting names from email addresses, that would be a very popular video?! OH, of course I also need those functions to apply to a whole column range! Does this make sense?! If you can help in any way, that would be amazing, and as I said, I reckon you'd be even more popular!! :) Thank you in advance (PS: for me I cannot use a plugin for this!)
@jezzgrainger139 Ай бұрын
Have no fear! Have worked it out using INDEX, SUBSTITUTE, SPLIT and PROPER! Happy dayzzzzz
@cgc2300 Ай бұрын
hello I would like to ask you a question about API I made a product catalog on Google Sheet then I imported this catalog into WordPress I was wondering if it would be possible thanks to the combination of Google Sheet APIs, WordPress and the use of n8n software that all the keywords present in the description column of each product are automatically replaced by the correct keywords? and the same for images? THANKS
@coupleracademy Ай бұрын
Hey there! 👋 Thanks for your question! Yes, you can definitely achieve this with a combination of Google Sheets API, WordPress, and n8n. Here’s a basic outline of how you can do it: 1. Google Sheets API: Use it to fetch your product catalog data. 2. n8n Workflow: Set up n8n to automate the process. You can create a workflow that: - Fetches data from Google Sheets. - Identifies and replaces keywords in the description column. - Updates images accordingly. 3. WordPress Integration: Use WordPress API to update the product details on your website with the modified data from n8n. By setting up this workflow, you can automate the process of replacing keywords and updating images in your product descriptions.
@cgc2300 Ай бұрын
cool
@coupleracademy Ай бұрын
glad you think that!
@cgc2300 Ай бұрын
Hello I made a product catalog on Google Sheet then I imported this catalog into WordPress I was wondering if it would be possible thanks to the combination of Google Sheet APIs, WordPress and the use of n8n software that all the keywords present in the description column of each product are automatically replaced by the correct keywords? and the same for images? THANKS
@joemygawd Ай бұрын
Could this be possible to do from a google sheet to google calendar?
@coupleracademy Ай бұрын
If you need to connect Google Calendar to Google Sheets we have a video on this kzbin.info/www/bejne/fZnbZZVnaZlnf7M But if you want to connect Google Sheets to Google Calendar you may try to do it with Zapier kzbin.info/www/bejne/anqXc3ywpL9kf5Y
@hiteshshah2638 Ай бұрын
Thank you for this video. Really helpful. Just one question, s there a way to remove Table header (Date Start (Year Month) / Total Clicks) in this video.
@coupleracademy Ай бұрын
Hi, thanks a lot! No such way in the settings, strangely, but you can use a workaround - set the table header and header background colors to be the same and the header will magically disappear :) See an example - monosnap.com/file/Yi5qIBwYUD12R04CuimEQhmgmVROEh
@onlytanofficial Ай бұрын
This tutorial is so good!!! You just saved my day!! Thank you so much!!
@coupleracademy Ай бұрын
Lovely to hear that, thanks! :)
@agoogleuser6937 Ай бұрын
Please enable airtable as a destination
@coupleracademy Ай бұрын
Hi! We were seriously considering it but currently its API doesn't offer the functionality we would need to send data to Airtable. For now Coupler.io can only fetch data from Airtable. If it's any useful, consider signing up for a free account and you'll be the first to hear if we add Airtable as a destination.
@agoogleuser6937 9 күн бұрын
ByteLine allows 2-way sync from/to airtable
@JK-fh6zf Ай бұрын
Super helpful!👍
@coupleracademy Ай бұрын
Awesome to hear that, thanks!
@nicxonpiaso6199 Ай бұрын
I don't understand whether this is MS Access Database alternative..This is another Excel spreadsheet.. I'm lost trying to understand this..🤔😢
@coupleracademy Ай бұрын
Google Sheets is basically Google's version of Excel - not as powerful but definitely easier to grasp :)
@NihalSingh-ml3ij Ай бұрын
Good job buddy. Satyam ne thankyou bola hai bhai ko!
@coupleracademy Ай бұрын
Hey, thanks a lot! 😊 Satyam, aapka bahut dhanyavaad! Glad you found the video helpful. If you have any other questions about VLOOKUP or anything else in Google Sheets, just let us know!
@ssekyanzipatrick6862 Ай бұрын
Thanks
@coupleracademy Ай бұрын
You're welcome!
@digital-rushikesh Ай бұрын
plz also make a video on connecting instagram also
@coupleracademy Ай бұрын
2 videos on Instagram are coming up in the next few weeks. Be sure to subscribe so you don't miss them!
@JohnnyKenimer Ай бұрын
Shaina twain's video Sex tapes now
@barbarachappuis766 Ай бұрын
Very helpful. Thank you!
@coupleracademy Ай бұрын
Glad to hear that, thanks! :)
@panathenomad Ай бұрын
Soooo helpful! Thank you for this!
@coupleracademy Ай бұрын
You're so welcome!
@ayissingkek3627 Ай бұрын
I just say " Kulo ora iso boso luar"
@coupleracademy Ай бұрын
Halo, seneng banget iso krungu saka sampeyan! Sayangé, kita durung duwe konten ing basa sampeyan, nanging coba fitur auto-translate saka KZbin. Lan ojo lali kanggo langganan kanggo konten apik liyane!
@h0cusf0cus Ай бұрын
if i have names and promotions and dates, how do i get the dates to stick to the name/row? whenever someone changes their name, the dates start moving around and all the dates get messed up. the dates arnt sticking to each name.
@coupleracademy Ай бұрын
Hey there! Sounds frustrating! To keep dates from moving around when names change, try these tips: - Use Unique IDs: Add a unique ID column for each row to ensure each entry is distinct. - Sort Carefully: Always sort by the ID or another stable column to maintain row integrity. - Use Data Validation: Set up data validation to prevent unwanted changes to the names. This way, your dates should stay in place with the correct names. Let us know if you need more help!