asked on

Help to create script to calculate data from Google Sheet

michalek19 · 2023-09-22T00:49:19Z

Serverlist.xlsx Hi, I need help to improve the” script” in Google Sheet to be able to 1. Exclude from column “MANUFACTURER” : VMware, Azure and Aws. So, they are not included in the calculation cores in task 2 (below) 2. Calculate how many cores were assigned to Physical and Virtual servers (Column INSTANCE TYPE) and define OPERATING SYSTEM names for each from (column OPERATING SYSTEM new). 3. Calculate cores total excluding VMware, Azure and Aws (Column MANUFACTURER) Or If there is an easier way to get these information perhaps using Powershell or VB please assist. Here is what I wrote for Google sheet. It works, but I am not getting the results I expected. function myFilter() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var Serverlist = ss.getSheetByName("Servers"); var dRng = Serverlist.getRange(2, 1, Serverlist.getLastRow(),44).getValues(); var Criteria1 = Serverlist.getRange ("A31").getValue(); var Criteria2 = Serverlist.getRange ("B31").getValue(); var fData = dRng.filter(function(e){return (e)[3]== Criteria1 && e[32]==Criteria2}); var newSht = ss.insertSheet(); newSht.getRange(2, 1, fData.length, 44).setValues(fData); newSht.getRange ("AA:AA").setNumberFormat("0"); var headers = Serverlist.getRange ("A1:AR1").getValues(); newSht.getRange ("A1:AR1").setValues(headers); } What i need sample Output : SERVER NAME INSTANCE TYPE OPERATING SYSTEM (new) CORES Total Core Datacente Total Core Standard ABS2 Physical Microsoft Windows Server 2008 R2 Standard x64 4 12 23 ABS3 Physical Microsoft Windows Server 2008 R2 Standard x64 4 ABS4 Physical Microsoft Windows Server 2008 R2 Standard x64 Service Pack 1 1 ABS5 Physical Microsoft Windows Server 2012 R2 1 ABS6 Physical Microsoft Windows Server 2012 R2 Standard x64 1 ABS7 Physical Microsoft Windows Server 2016 1 ABS8 Physical Microsoft Windows Server 2016 (64-bit) 2 ABS9 Physical Microsoft Windows Server 2016 Datacenter x64 2 ABS10 Physical Microsoft Windows Server 2016 Standard x64 2 ABS11 Physical Microsoft Windows Server 2019 2 ABS12 Physical Microsoft Windows Server 2019 Standard x64 2 ABS13 Physical Microsoft Windows Server 2022 8 ABS14 Virtual Microsoft Windows Server 2008 R2 Standard x64 8 ABS15 Virtual Microsoft Windows Server 2008 R2 Standard x64 2 ABS16 Virtual Microsoft Windows Server 2008 R2 Standard x64 Service Pack 1 1 ABS17 Virtual Microsoft Windows Server 2012 R2 2 ABS18 Virtual Microsoft Windows Server 2012 R2 Standard x64 4 ABS19 Virtual Microsoft Windows Server 2016 2 ABS20 Virtual Microsoft Windows Server 2016 (64-bit) 4 ABS21 Virtual Microsoft Windows Server 2016 Datacenter x64 1 ABS22 Virtual Microsoft Windows Server 2016 Standard x64 1 ABS23 Virtual Microsoft Windows Server 2019 1 ABS24 Virtual Microsoft Windows Server 2019 Standard x64 3 ABS25 Virtual Microsoft Windows Server 2022 2 l Thx, Michal

Serverlist.xlsx

Open in new window

Hi,

I need help to improve the” script” in Google Sheet to be able to

1. Exclude from column “MANUFACTURER” : VMware, Azure and Aws. So, they are not included in the calculation cores in task 2 (below)

2. Calculate how many cores were assigned to Physical and Virtual servers (Column INSTANCE TYPE) and define OPERATING SYSTEM names for each from (column OPERATING SYSTEM new).

3. Calculate cores total excluding VMware, Azure and Aws (Column MANUFACTURER)

If there is an easier way to get these information perhaps using Powershell or VB please assist.

Here is what I wrote for Google sheet. It works, but I am not getting the results I expected.

function myFilter() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var Serverlist = ss.getSheetByName("Servers");

var dRng = Serverlist.getRange(2, 1, Serverlist.getLastRow(),44).getValues();

var Criteria1 = Serverlist.getRange ("A31").getValue();

var Criteria2 = Serverlist.getRange ("B31").getValue();

var fData = dRng.filter(function(e){return (e)[3]== Criteria1 && e[32]==Criteria2});

var newSht = ss.insertSheet();

newSht.getRange(2, 1, fData.length, 44).setValues(fData);

newSht.getRange ("AA:AA").setNumberFormat("0");

var headers = Serverlist.getRange ("A1:AR1").getValues();

newSht.getRange ("A1:AR1").setValues(headers);

}

What i need sample Output :

SERVER NAME	INSTANCE TYPE	OPERATING SYSTEM (new)	CORES	Total Core Datacente	Total Core Standard
ABS2	Physical	Microsoft Windows Server 2008 R2 Standard x64	4	12	23
ABS3	Physical	Microsoft Windows Server 2008 R2 Standard x64	4
ABS4	Physical	Microsoft Windows Server 2008 R2 Standard x64 Service Pack 1	1
ABS5	Physical	Microsoft Windows Server 2012 R2	1
ABS6	Physical	Microsoft Windows Server 2012 R2 Standard x64	1
ABS7	Physical	Microsoft Windows Server 2016	1
ABS8	Physical	Microsoft Windows Server 2016 (64-bit)	2
ABS9	Physical	Microsoft Windows Server 2016 Datacenter x64	2
ABS10	Physical	Microsoft Windows Server 2016 Standard x64	2
ABS11	Physical	Microsoft Windows Server 2019	2
ABS12	Physical	Microsoft Windows Server 2019 Standard x64	2
ABS13	Physical	Microsoft Windows Server 2022	8
ABS14	Virtual	Microsoft Windows Server 2008 R2 Standard x64	8
ABS15	Virtual	Microsoft Windows Server 2008 R2 Standard x64	2
ABS16	Virtual	Microsoft Windows Server 2008 R2 Standard x64 Service Pack 1	1
ABS17	Virtual	Microsoft Windows Server 2012 R2	2
ABS18	Virtual	Microsoft Windows Server 2012 R2 Standard x64	4
ABS19	Virtual	Microsoft Windows Server 2016	2
ABS20	Virtual	Microsoft Windows Server 2016 (64-bit)	4
ABS21	Virtual	Microsoft Windows Server 2016 Datacenter x64	1
ABS22	Virtual	Microsoft Windows Server 2016 Standard x64	1
ABS23	Virtual	Microsoft Windows Server 2019	1
ABS24	Virtual	Microsoft Windows Server 2019 Standard x64	3
ABS25	Virtual	Microsoft Windows Server 2022	2

Thx, Michal

Rob

Any reason you couldn't just add another column with the formula: =IF(ISNA(MATCH(K2,{"VMware","Azure","AWS"},0)),AA2,0)

then just create a pivot table

Serverlist.xlsx

michalek19

ASKER

Where i should insert this formula? should i add formula to the existing script?

michalek19

ASKER

I using Google Sheet. Where I should add this

IF(ISNA(MATCH(K2,{"VMware","Azure","AWS"},0)),AA2,0)

This is what so far i have

unction myFilter() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var Serverlist = ss.getSheetByName("Servers");

var dRng = Serverlist.getRange(2, 1, Serverlist.getLastRow(),44).getValues();

var Criteria1 = Serverlist.getRange ("A31").getValue();

var Criteria2 = Serverlist.getRange ("B31").getValue();

var fData = dRng.filter(function(e){return (e)[3]== Criteria1 && e[32]==Criteria2});

var newSht = ss.insertSheet();

newSht.getRange(2, 1, fData.length, 44).setValues(fData);

newSht.getRange ("AA:AA").setNumberFormat("0");

var headers = Serverlist.getRange ("A1:AR1").getValues();

newSht.getRange ("A1:AR1").setValues(headers);

}

Rob

no need for the script, unless i'm missing something.

Here it is as a google sheet: https://docs.google.com/spreadsheets/d/1SW06yJB1Q45dKDGl5c_8nBnhWBE3bexctMplTdcitJk/edit?usp=sharing

michalek19

ASKER

did you created a new filter? is possible to show how many ms 2016 datacenter servers, ms 2012 standard servers, etc. and number of cors.

Rob

Have a look on the next tab "by Operating System"

michalek19

ASKER

can you also create by instance

Rob

take a look at that

michalek19

ASKER

How did you do that? have you used functions or script?

michalek19

ASKER

Can I copy your steps? if yes where i can find them?

michalek19

ASKER

I was able to recreated.

How I can see total cores in "by server? Sheet must include Host name, instance type, manufacturer, what MS OS is assigned to each server, Core numbers. Can you exclude manufacturer AWS, AZURE, AWS? Can you exclude OS from the count like, RHEL, Ubundu, NetApp onTap

How I can see core total in "by Operating Systems". I want to see only Microsoft product that is associated with Instance Type and Manufacturer (HPE , - , Rackspace ( column K -Manufacturer) . Can you exclude AWS, AZURE, AWS?

How I can see core total in "by instance". I want to see cores total for only physical and virtual instance. Can you exclude AWS, AZURE, AWS from the count?

Rob

Yes it's possible to do all that and just with pivot tables. You can add filters to the pivot to exclude things from the count.

In fact that's the easiest way and you can just have one pivot

michalek19

ASKER

can you help me with that

ASKER CERTIFIED SOLUTION

Rob

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer