Google Sheets Question

[tomoki]

Hi there,

 

I've read upon how Google Sheets uses their servers to do the calculations instead of having your own personalized computer do it. My colleague says he recently found a button within Google Sheets to add Javascript functions and hopefully it would decrease the time/latency needed in order to calculate a specific cell, vlookup/hlookup from sheets to sheets and also refresh the sheets more quickly. My question is, would Javascript functions actually decrease the amount of time needed for Google's servers to calculate all the stuff on Sheets? 

 

FYI these sheets are for company use so there's a lot of stuff on each sheet and that there are more than 5 sheets. I've also found this one button specifically to change the refresh times into "Google Finance" but if the sheets had a formula that contained "Importrange", it would be different I think. 

 

My idea to him however is that we'd need better hardware in order to receive information quicker. Our internet is "decent" imo and I think we're lacking in hardware speeds. That being said though, it doesn't have much affect between personalized computers and Google's Servers I would hope to assume. 

[aeroencychris]

For the sake of your situation and simplicity, I would just keep it running through Google. I can already tell Javascript will just be a headache. Cool idea though.

[tomoki]

Just now, cdsboy2000 said:

For the sake of your situation and simplicity, I would just keep it running through Google. I can already tell Javascript will just be a headache. Cool idea though.

Oh no but this is JavaScript within Google Sheets. It's supported by Sheets.

[bgibbz]

Just use Microsoft excel if this is for a business, google sheets isn't really for business use and exec offers far more functionality. 

[tomoki]

Just now, bgibbz said:

Just use Microsoft excel if this is for a business, google sheets isn't really for business use and exec offers far more functionality. 

Sounds like a good option to me and I know you can share workbooks but um... how does that work exactly? Who hosts the server? Are there logins and such? 

 

I should look into this

[bgibbz]

Microsoft excel is client side. It's a physical document on ones computer. All of the data gets proceeded and competed locally. You can use any cloud storage medium to hold the document. 

[tomoki]

1 minute ago, bgibbz said:

Microsoft excel is client side. It's a physical document on ones computer. All of the data gets proceeded and competed locally. You can use any cloud storage medium to hold the document. 

Hmm... yup I get that excel is Client based and is physically stored on one's computer. Yes it makes sense that the data gets run through locally but for cloud storage, you mean I'd need something like a small NAS unit to store the file. If that's the case, I'm not exactly sure how that works. I might need more explanation. Sorry! 

 

What's going on in my head:

- Workbook file is stored in NAS

- Users access the file and make changes

- What calculates the file stored in the NAS? 

- If it's stored in the NAS, how do I make regular backups of it in case the NAS dies someday? 

 

One thing I don't really like about this idea is that the tracking changes function isn't similar to the Google Sheets history tracker. That's a downside I feel. 

[bgibbz]

18 minutes ago, tomoki said:

Hmm... yup I get that excel is Client based and is physically stored on one's computer. Yes it makes sense that the data gets run through locally but for cloud storage, you mean I'd need something like a small NAS unit to store the file. If that's the case, I'm not exactly sure how that works. I might need more explanation. Sorry! 

 

What's going on in my head:

- Workbook file is stored in NAS

- Users access the file and make changes

- What calculates the file stored in the NAS? 

- If it's stored in the NAS, how do I make regular backups of it in case the NAS dies someday? 

 

One thing I don't really like about this idea is that the tracking changes function isn't similar to the Google Sheets history tracker. That's a downside I feel. 

I wish I could answer your questions, but I'm not entirely sure if the answers my self. My brother is the resident expert on excel for business, and he isn't available right now. If I figure it out, up let you know. i think wherever the file is store is the computer that goes through the calculations. How many people do you need to access the document at the same time? Most business scenarios I've seen seem to involve only 1 primary editor, and the document getting emailed around to the nessacary people. 

[tomoki]

Just now, bgibbz said:

I wish I could answer your questions, but I'm not entirely sure if the answers my self. My brother is the resident expert on excel for business, and he isn't available right now. If I figure it out, up let you know. i think wherever the file is store is the computer that goes through the calculations. How many people do you need to access the document at the same time? Most business scenarios I've seen seem to involve only 1 primary editor, and the document getting emailed around to the nessacary people. 

Yep currently we have 1 primary editor for our Google Sheets and we have at least 4 people interacting with it at the same time. Sometimes there are more whenever other teams require to check upon stuff. Emailing the document is not an option for us unfortunately. It would be highly appreciated if you could ask!  

[bgibbz]

2 minutes ago, tomoki said:

Yep currently we have 1 primary editor for our Google Sheets and we have at least 4 people interacting with it at the same time. Sometimes there are more whenever other teams require to check upon stuff. Emailing the document is not an option for us unfortunately. It would be highly appreciated if you could ask!  

Just found this- https://support.office.com/en-us/article/Use-a-shared-workbook-to-collaborate-49b833c0-873b-48d8-8bf2-c1c59a628534?ui=en-US&rs=en-001&ad=US

 

essentially you can place the document on a shared location or on a cloud storage like Microsoft sky drive or even google drive and multiple users can edit simultaneously. 

[tomoki]

2 minutes ago, bgibbz said:

Just found this- https://support.office.com/en-us/article/Use-a-shared-workbook-to-collaborate-49b833c0-873b-48d8-8bf2-c1c59a628534?ui=en-US&rs=en-001&ad=US

 

essentially you can place the document on a shared location or on a cloud storage like Microsoft sky drive or even google drive and multiple users can edit simultaneously. 

Oh yeah I was reading upon that when I tried looking it up. Essentially there are things I cannot do whenever the workbook is online and being shared. I'm looking into Microsoft Sharepoint services because that's the "cloud" service that Excel workbooks are supposedly connected to. I'm not sure if our company has it. I can check tomorrow when I get the time to fiddle around during downtime. 

 

The upside is that our company does have a Lynda.com login for employees so I'll be ...taking advantage of that to the best of my ability. I'm just another employee who has some idea of technology. Of course, it's never inclusive enough of all aspects so that's why I'm on here asking people who would have a higher chance of knowing what they're doing. 

 

I'll be asking the owner of our current Google Sheets and asking him to make a copy of the whole thing for me to fiddle. Hopefully I can rebuild it in Excel. 

 

Our company uses Microsoft Dynamics AX primarily but then it lags like hell cause it's extremely customized. The IT team is looking into improving it this coming week but I doubt that'll be fast enough still. The IT team hopes to remove us from using Google Sheets completely by early 2017. I think that's a good outlook but I am sorta doubting their ability at the same time because it shouldn't take that long even with testing...to create buttons and shorten formulas. I might be heading down a very redundant path by myself but only trying to help and I guess... learning isn't so bad because it's something I can eventually take away with me. 

 

What I'm most curious about at this point is if I can do all of this in a relatively short period of time. It'd be great if I could get rid of their current problems before Christmas arrives. 

[vorticalbox]

how much latency are we taking here? 

[tomoki]

Just now, vorticalbox said:

how much latency are we taking here? 

The input does work, everyone can see the input but the update towards all the formulas and updates on everyone else's screens after the formulas are done processing ...take around a couple mins. Sometimes it might not load so pages have to be refreshed. 

[vorticalbox]

41 minutes ago, tomoki said:

The input does work, everyone can see the input but the update towards all the formulas and updates on everyone else's screens after the formulas are done processing ...take around a couple mins. Sometimes it might not load so pages have to be refreshed. 

 

see problem is that I am unsure how multi-working on office works. I believe the computer that made the change has to deal with the processing.

[tomoki]

Just now, vorticalbox said:

see problem is that I am unsure how multi-working on office works. I believe the computer that made the change has to deal with the processing.

Oh currently we have a program that works all our inventory, sales etc...  but it's just way too slow because of how highly customized it is. That's why others in the office brought up Google Sheets. Before I started working here, they already finished building the Google Sheets. ...well 1 guy did it all. 

 

Wait so now the calculations are based on local cpu processes? If that's so, I will have to speak with the IT team because I'm honestly curious as to why they picked the hardware they did for us. 

[vorticalbox]

11 minutes ago, tomoki said:

Oh currently we have a program that works all our inventory, sales etc...  but it's just way too slow because of how highly customized it is. That's why others in the office brought up Google Sheets. Before I started working here, they already finished building the Google Sheets. ...well 1 guy did it all. 

 

Wait so now the calculations are based on local cpu processes? If that's so, I will have to speak with the IT team because I'm honestly curious as to why they picked the hardware they did for us. 

 

 

 

they are for things like Microsoft Office.

 

in fact give this a read. http://developer.amd.com/community/blog/2015/07/15/collaboration-and-open-source-at-amd-libreoffice/

LibreOffice can use openCL for calculations and might be worth a look.

[tomoki]

7 hours ago, vorticalbox said:

they are for things like Microsoft Office.

 

in fact give this a read. http://developer.amd.com/community/blog/2015/07/15/collaboration-and-open-source-at-amd-libreoffice/

LibreOffice can use openCL for calculations and might be worth a look.

Hey @vorticalbox

Thanks for this link! It's nice that openCL can be used to increase calculation speeds and all but we unfortunately have business laptops that use...integrated graphics. I'm not sure how openCL would handle that as opposed to the APU's that AMD has.. which are obviously better for this case scenario. 

 

On a side note however, I've been monitoring our Google Sheets and every time it loads, my CPU usage spikes and my audio begins to lag. Business laptops suck tbh but apparently there's this longevity thing to it that IT support loves. I'm not even sure why. It's stupid imo. 

[vorticalbox]

13 minutes ago, tomoki said:

Hey @vorticalbox

Thanks for this link! It's nice that openCL can be used to increase calculation speeds and all but we unfortunately have business laptops that use...integrated graphics. I'm not sure how openCL would handle that as opposed to the APU's that AMD has.. which are obviously better for this case scenario. 

 

On a side note however, I've been monitoring our Google Sheets and every time it loads, my CPU usage spikes and my audio begins to lag. Business laptops suck tbh but apparently there's this longevity thing to it that IT support loves. I'm not even sure why. It's stupid imo. 

in which case it seems like sheets uses your cpu. Actually even integrated graphics would be better than a cpu for number crunching. 

 

[tomoki]

Just now, vorticalbox said:

in which case it seems like sheets uses your cpu. Actually even integrated graphics would be better than a cpu for number crunching. 

 

Yeah at first I was thinking of using a RAM cache but because Google Chrome + Sheets ..and all the other tabs that I need to have opened (other Sheets), use so much RAM, I can't have a ram cache going. It's sucks haha. I have a max of 8gb RAM on this laptop and other laptops that my coworkers are using have the same or less than this amount of RAM. Third party ram cache programs use 4gb of ram by default and cannot be changed. 

 

So with that aside, I can't actually do much other than search for possible alternatives. I've been looking into Sharepoint but still am not sure whether we have that support with our "Office 365 ProPlus". It says we do, yet I don't see it already installed onto our computers. I'll be having a meeting this week with the guy who made our Google Sheets... as well as my supervisor... and I'll definitely bring all these problems up. 

 

FYI the cpu on my laptop ... "m5 - 6Y54 @1.10 GHz" I have never heard of it before I started using this.