Author: Math Dev

How to make Google sheets refresh date filtered pivot tables when a day passes?

Post author By Math Dev
Post date January 23, 2021
No Comments on How to make Google sheets refresh date filtered pivot tables when a day passes?

Google Sheets has a nice advantage over Excel – their pivot table automatically refresh, while Excel’s pivot tables rely on clicking a manual UI refresh button (or – if the right pivot setting is changed from its default state – by reopening the relevant file). But Google Sheets’ pivot tables do not get refreshed in […]

Tags Google Sheets has a nice advantage over Excel - their pivot table automatically refresh, in this case). Is there a way around it?, today()) 1 Bar =RANDBETWEEN(today()-1, today()) 2 And add a date filter of today: Let's say luck has it that on the original run you get: | Name | Date | Value | | -------- | --, unless you change something else of course. That's because Google Sheets' pivot tables don't recognize the change of days (when the clock sp, while Excel's pivot tables rely on clicking a manual UI refresh button (or - if the right pivot setting is changed from its default state - b

User Help Web Apps

Rename multiple files in google drive from a spreadsheet

Post author By Math Dev
Post date January 23, 2021
No Comments on Rename multiple files in google drive from a spreadsheet

I have this spreadsheet which contains the name of multple files in my google drive Column one contains the current names of the files and column two contains the new names i want to assign to each file Using google app script i could do this manually by copying the old names one after the […]

Tags I have this spreadsheet which contains the name of multple files in my google drive Column one contains the current names of the files and co

User Help Web Apps

Remove WhatsApp images from OneDrive photo library

Post author By Math Dev
Post date January 23, 2021
No Comments on Remove WhatsApp images from OneDrive photo library

I’m trying to remove WhatsApp clutter from my private photos folder on OneDrive. I’m using OneDrive on Android and it seems it is being used as the backup target for WhatsApp and Viber. I really need a suggestion on how to remove the whole WhatsApp lot from OneDrive and stop syncing new ones. If there […]

Tags I'm open to creating a shared script on github., I'm trying to remove WhatsApp clutter from my private photos folder on OneDrive. I'm using OneDrive on Android and it seems it is being used

User Help Web Apps

How to format this result in google sheet as a table again after importxml of nested table?

Post author By Math Dev
Post date January 23, 2021
No Comments on How to format this result in google sheet as a table again after importxml of nested table?

I used this formula in google sheets =importxml("https://www.investing.com/equities/aboitiz-equity-balance-sheet","//tr") However, however it is a nested table, so the values are not formated pro Symbol Exchange Currency AEV Philippines PHP Delayed ABTZY OTC Markets USD Delayed Period Ending: 202030/09 202030/06 202031/03 201931/12 Total Current Assets 149486.88 157703.15 157060.05 124425.29 **" Cash and Short Term Investments 65403.14 75288.51 […]

Tags "//tr") However, however it is a nested table, I used this formula in google sheets =importxml("https://www.investing.com/equities/aboitiz-equity-balance-sheet", Net 38594.89 39947.7 35037.43 35195.59 Accounts Receivables - Trade, Net 38594.89 39947.7 35037.43 35195.59 Total Inventory 22785.02 22964.64 24199.83 23347.72 Prepaid Expenses - - - - Other Current Assets, so the values are not formated pro Symbol Exchange Currency AEV Philippines PHP Delayed ABTZY OTC Markets USD Delayed Period Endi, Total 22703.83 19502.29 21026.12 19457.31 "** Cash and Short Term Investments 65403.14 75288.51 76796.66 46424.66 Cash - - - - Cash & Equival

User Help Web Apps

ImportHTML number of tables keeps changing

Post author By Math Dev
Post date January 23, 2021
No Comments on ImportHTML number of tables keeps changing

can someone help me? I use the google sheets importHTML function to scrap data from the website, but the number of tables keeps changing. How to set it so that it gets the correct table every time?

Tags but the number of tables keeps changing. How to set it so that it gets the correct table every time?, can someone help me? I use the google sheets importHTML function to scrap data from the website

User Help Web Apps

Combine two separate, working queries

Post author By Math Dev
Post date January 23, 2021
No Comments on Combine two separate, working queries

Trying to combine two separate, working queries, so both can be used on a single sheet. The idea is a User selects Data for a Resource (sheet TAB), and then either: a Region (Column D) or a County (Column H). Both Region and County are Drop Down lists. These are the Queries that work separately: […]

Tags " AND (D) = '"&A2&"' AND (C2)="-" OR (D) = 'All Counties' " AND (C2)="-" OR C2="All Regions", " AND (D) = '"&A2&"' OR (D) = 'All Counties' "), " AND (H) = '"&C2&"' AND (A2)="-" OR (H) = 'All Regions' AND (A2)="-" "), " AND (H) = '"&C2&"' OR (H) = 'All Regions' "), "", "SELECT * WHERE 1=1 " &IF(A2="All Counties", "SELECT * WHERE 1=1 " &IF(C2="All Regions", 3) =QUERY(indirect(B2&"!A1:H20"), 3) Also tried similar to above, 3) Working Queries are on the first 2 TABs of this sheet I have tried to join them: ={QUERY(indirect(B2&"!A1:H20"), 3)} Also tried a few things like this with various syntax changes: =QUERY(indirect(B2&"!A1:H20"), and then either: a Region (Column D) or a County (Column H). Both Region and County are Drop Down lists. These are the Queries that work sep, but used ISBLANK(C2) type syntax. Thank you for taking the time to try and help!, so both can be used on a single sheet. The idea is a User selects Data for a Resource (sheet TAB), Trying to combine two separate, working queries

User Help Web Apps

How can gaps be quantified within a combined set of variable arrays?

Post author By Math Dev
Post date January 23, 2021
No Comments on How can gaps be quantified within a combined set of variable arrays?

I’m using Google Sheets to build a crop planning tool for farmers (kinda like a gantt chart). I need help quantifying the number of days within a given timeframe that no crops are planted in a bed. In the end, I want to be able to be able to reference a single bed by name […]

Tags I want to be able to be able to reference a single bed by name within the table (e.g. Bed 1 or NW4) and know the total number of "unplanted", I'm using Google Sheets to build a crop planning tool for farmers (kinda like a gantt chart). I need help quantifying the number of days with, step 2 is proving to be especially difficult. I can't figure out how to make a combined array that's extrapolated from a variable array. Ya d

User Help Web Apps

How can gaps be quantified within a combined set of variable arrays?

Post author By Math Dev
Post date January 23, 2021
No Comments on How can gaps be quantified within a combined set of variable arrays?

Tags I want to be able to be able to reference a single bed by name within the table (e.g. Bed 1 or NW4) and know the total number of "unplanted", I'm using Google Sheets to build a crop planning tool for farmers (kinda like a gantt chart). I need help quantifying the number of days with, step 2 is proving to be especially difficult. I can't figure out how to make a combined array that's extrapolated from a variable array. Ya d

User Help Web Apps

How can gaps be quantified within a combined set of variable arrays?

Post author By Math Dev
Post date January 23, 2021
No Comments on How can gaps be quantified within a combined set of variable arrays?

Tags I want to be able to be able to reference a single bed by name within the table (e.g. Bed 1 or NW4) and know the total number of "unplanted", I'm using Google Sheets to build a crop planning tool for farmers (kinda like a gantt chart). I need help quantifying the number of days with, step 2 is proving to be especially difficult. I can't figure out how to make a combined array that's extrapolated from a variable array. Ya d

User Help Web Apps

Placeholders in a FROM Clause in SQL query. like in Python string Formating [closed]

Post author By Math Dev
Post date January 23, 2021
No Comments on Placeholders in a FROM Clause in SQL query. like in Python string Formating [closed]

select * , RANK() OVER (ORDER BY CAST(batchid AS INT) DESC) AS RNK FROM {0}.tablename I am new to coding and would like to where "0" holding the database name is {0} is supposed to be in my code. In python it is always in the ".format(….)" but this is a sql code and part […]

Tags RANK() OVER (ORDER BY CAST(batchid AS INT) DESC) AS RNK FROM {0}.tablename I am new to coding and would like to where "0" holding the databa, select")