#excel macro

[ma-tsu-the-male-goddess]

We’re all animals who need enrichment in our cages. Sometimes that enrichment is making art or destroying something.

Sometimes it’s figuring out how Google sheets macro coding works so you can build a little program for your brother to enter in his win/loss ratio for Balatro, broken down by Stake & Deck.

Let me know if you want a copy of the file for whatever reason. It’s fun :) and I’m happy to make changes for you.

Tumblr-friendly text & cleaned up macro code & expanded Sheets formulas at bottom of the readmore & explanations if you’re interested.

I know it’s not optimized or whatever but I’m new to JavaScript and I’m used to using Excel instead of Google sheets.

Google Sheets Macro aka Google Apps Script aka JavaScript:

What that code would look like with and without variables :(

Google Sheets in cell formulas:

(I know they look ridiculous)

+ expanded view so it’s a little easier to follow

(Color-coded in-line text Tumblr-friendly version in Pic 4 ID below.)

+ explanations!

Google Sheets Macro AKA JavaScript Coding

[Picture 1 Text ID]

Macro Code Text: Comments are in blue.

function sendQuote() { //Function named sendQuote

//Set Variables

var ui = SpreadsheetApp.getUi();

var Deck = ui.prompt("Deck Type").getResponseText(); //prompt

var Stake = ui.prompt("Stake Level").getResponseText(); //prompt

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

var sheetSub = spreadsheet.getSheetByName("Submission & Dashboard");

var sheetEntry = spreadsheet.getSheetByName("List of Game Entries");

const WinLose = ui.alert("Did you win?", ui.ButtonSet.YES_NO); //prompt

//Check WinLose condition

if (WinLose === ui.Button.YES) {

var WinLoseNum = 1;

var WinLoseVar = 'Win';

ui.alert('Yay!');

}

else {

var WinLoseNum = 0;

var WinLoseVar = 'Loss';

ui.alert('Loser.');

}

//Check if correct info

const response = ui.alert("Is this info correct?\nDeck Type: "+ Deck + "\nStake Level: " + Stake + "\nResult: " + WinLoseVar, ui.ButtonSet.YES_NO );

//Could have probably done "Is this info correct?\nDeckType: ${Deck}\nStake Level: ${Stake}\nResult: ${WinLoseVar}"

//Set Cell Variables

var cellValue = sheetSub.getRange("B3").getValue(); //gives max # of current Responses

var Row = cellValue + 5; //Gives next available line row

if (response === ui.Button.YES) { //If info is correct, do this

var Comments = ui.prompt("Add any run notes here.").getResponseText();

ui.alert("Response Submitted");

//Fill Table Values

sheetEntry.getRange("B"+ Row).setValue(cellValue + 1);

sheetEntry.getRange("C"+ Row).setValue(Deck);

sheetEntry.getRange("D"+ Row).setValue(Stake);

sheetEntry.getRange("E"+ Row).setValue(WinLoseNum);

sheetEntry.getRange("F"+ Row).setValue(Comments);

}

else { //If info is not correct, exit & try again

ui.alert("Try Again!");

}

}

/*

Comment Blocks

*/

[End Picture 1 ID]

[Picture 2 Text ID]

Google Apps Script is based on JavaScript so it’s easiest to look up 'how to do x in JavaScript' than google sheets.

Functions Used

getUi()

prompt(string)

getResponseText()

getActiveSpreadsheet()

getSheetByName(string)

alert(string, ButtonSet)

getRange()

getValue()

setValue(variable)

If() {}

Else {}

//let - can't be redefined in scope

//var - value can change in scope

//const - value can't be changed

You can make your own functions to make your code more readable:

function repeatBack () {

var ui = SpreadsheetApp.getUi();

var = wordsPlease = ui.prompt("What would you like me to say?").getResponseText();

sayTo(wordsPlease);

}

function sayTo (wordsToSayBack) {

ui.alert(wordsToSayBack);

}

Haven't tested this one yet:

function sayHello(name = "Guest") {

console.log(`Hello, ${name}!`);

}

sayHello(); // Output: "Hello, Guest!"

sayHello("Bob"); // Output: "Hello, Bob!"

[End Picture 2 ID]

[Picture 3 Text ID]

How Macro Coding Functions Work

Functions using '.' in them act like location pointers.

var ui = SpreadsheetApp.getUi(); //Google Sheets UI

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); //Google Sheets UI -> Active Spreadsheet

var sheetEntry = spreadsheet.getSheetByName("List of Game Entries"); //Google Sheets UI -> Active Spreadsheet -> Sheet Named "List of Game Entries"

var sheetSub = spreadsheet.getSheetByName("Submission & Dashboard"); //Google Sheets UI -> Active Spreadsheet -> Sheet Named "Submission & Dashboard"

var cellValue = sheetSub.getRange("B3").getValue(); // Sheet Named "Submission & Dashboard" -> Cell Range (B3) -> Get Value

var Row = cellValue + 5; //B3 Value + 5

var Deck = ui.prompt("Deck Type").getResponseText(); //Google Sheets UI -> Prompt Function Class (Deck Type) -> Get Response Text

With Variables:

sheetEntry.getRange("B"+ Row).setValue(cellValue + 1); //Set next entry in column B to 'Row + 1 '

sheetEntry.getRange("C"+ Row).setValue(Deck); //Set next entry in column C to 'Deck'

Without Variables:

SpreadsheetApp.getActiveSpreadsheet().getSheetByName("List of Game Entries").getRange("B"+ cellValue + 5).setValue( SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Submission & Dashboard").getRange("B3").getValue() + 1);

SpreadsheetApp.getActiveSpreadsheet().getSheetByName("List of Game Entries").getRange("B"+ cellValue + 5).setValue(SpreadsheetApp.getUi().prompt("Deck Type").getResponseText());

[End Picture 3 ID]

Google Sheets In-cell Formulas

[Picture 4 Text ID]

In-Cell Formulas Breakdown

Table 1: (These values are formatted into percentages already)

IFERROR(IFERROR(SUM(FILTER(Game_Entries[Win / Loss],(Game_Entries[Deck] = $D23)*(Game_Entries[Stake] = E$5))),0) / IFERROR(COUNT(FILTER(Game_Entries[Win / Loss],(Game_Entries[Deck] = $D23)*(Game_Entries[Stake] = E$5))),0)), "--")

Table 2: (These values are displayed as text, so we format the 2nd half into percentages manually)

=IFERROR(SUM(FILTER(Game_Entries[Win / Loss],(Game_Entries[Deck] = $D23)*(Game_Entries[Stake] = E$5))),0)

& "/" &

IFERROR(COUNT(FILTER(Game_Entries[Win / Loss],(Game_Entries[Deck] = $D23)*(Game_Entries[Stake] = E$5))),0)

& " " &

IFERROR(IFERROR(SUM(FILTER(Game_Entries[Win / Loss],(Game_Entries[Deck] = $D23)*(Game_Entries[Stake] = E$5))),0) / IFERROR(COUNT(FILTER(Game_Entries[Win / Loss],(Game_Entries[Deck] = $D23)*(Game_Entries[Stake] = E$5))),0))*100, "--")

& "%"

[End Picture 4 ID]

[Picture 5 Text ID]

Cell Formulas Used:

IFERROR(Function, replacement value)

SUM(Values)

FILTER(Reference Table, Condition (Ref value = value))

Condition * Condition = 'And'

Condition + Condition = 'Or'

TableName[Column]

$A$1 - use to freeze column or row or both in functions - helpful when function used across a range

$D6 - Deck is all in column D

E$5 - Stake is all in row 5

"Words" & Value & "Words"

Other Options depending on context:

TEXTJOIN(delimiter, Ignore Empty, Text) - Joins with delimiter

TEXTJOIN(", ", TRUE, C1:C6, D2:D9) - This, That, These

CONCAT(Texts) - Joins list of values

CONCATENATE(Texts) - Joins several text strings into 1

[End Picture 5 ID]

[textless]

This fancy little wasp was the size of a small, elongated ant, and she was FAST. Go get it, teeny wasp.

Cochise County, Arizona, September 2024.

[ridethegarbagetruck]

Me when I see a cool rock in the grass

[mewwon]

fucking on this freaky hacker chick bodyy

[abowlofpetuniasandawhale]

“This is The Spreadsheet That Is Wrong And Everyone Hates!” They cry

“It is broken in strange and unusual ways that entrap good and wise men into stupid mistakes. Despite this, it is responsible for 80% of our kingdom’s profit somehow! This is why it is vital that you build a tool to replace The Spreadsheet That Is Wrong And Everyone Hates: because it is wrong and we hate it!”

But lo! Every time you present them with something that corrects one of the many issues of The Spreadsheet That Is Wrong And Everyone Hates, they complain that the numbers do not match The Spreadsheet That Is Wrong And Everyone Hates! You see, it must match The Spreadsheet That Is Wrong And Everyone Hates because The Spreadsheet That Is Wrong And Everyone Hates is responsible for 80% of our kingdom’s profit!”

[hope-ur-ok]

Let's automate the job setup system they said, it will make jobs faster to set up they said, totally won't take the same amount of time because the new form doesn't populate right so you're left having to track down all the missing info to put the job into quickbooks

[terrorbirb]

My dream is to make a calculator website with all the calculators I've come up with. I have calculators for manufacturing, sewing, uhm the combination of the two, and new calculators for my new job. I don't want to Google for calculators anymore, I just want a site that I can view on my phone and it's just ones for ME and people I teach about them.

I guess I want some modules too like stop watches.

[ereborne]

Song of the Day: December 11

“Only the Lonely Talkin” by Danielle Peck

[ave-immaculata]

me setting an app timer on tumblr to reduce screentime vs. me using tumblr on my computer to avoid the timer

[underforeversgrace]

i've accidentally become the excel queen at my job and I have 0 regrets.

i still dont know what the fuck a pivot table is.

[graysongarelick]

Elevate Client Services with Excel: Expert Tips for Financial Consultants by Grayson Garelick

Financial consultants operate in a dynamic environment where precision, efficiency, and client satisfaction are paramount. Excel, as a versatile tool, offers an array of features that can significantly enhance the services provided by financial consultants. Grayson Garelick, an accomplished financial analyst and consultant, shares invaluable Excel tips to help financial consultants elevate their client services and add tangible value.

The Role of Excel in Financial Consulting

Excel serves as the backbone of financial consulting, enabling consultants to analyze data, create models, and generate insights that drive informed decision-making. As the demands of clients become increasingly complex, mastering Excel becomes essential for financial consultants aiming to deliver exceptional services.

1. Customize Excel Templates

One of the most effective ways to streamline workflows and improve efficiency is by creating customized Excel templates tailored to specific client needs. Grayson suggests developing templates for budgeting, forecasting, and financial reporting that can be easily adapted for different clients, saving time and ensuring consistency.

2. Utilize PivotTables for Data Analysis

PivotTables are powerful tools in Excel that allow financial consultants to analyze large datasets and extract meaningful insights quickly. Grayson emphasizes the importance of mastering PivotTables for segmenting data, identifying trends, and presenting information in a clear and concise manner to clients.

3. Implement Conditional Formatting

Conditional formatting is a valuable feature in Excel that allows consultants to highlight important information and identify outliers effortlessly. By setting up conditional formatting rules, consultants can draw attention to key metrics, discrepancies, or trends, facilitating easier interpretation of data by clients.

4. Leverage Excel Add-ins

Excel offers a variety of add-ins that extend its functionality and provide additional features tailored to financial analysis and reporting. Grayson recommends exploring add-ins such as Power Query, Power Pivot, and Solver to enhance data manipulation, modeling, and optimization capabilities.

5. Automate Repetitive Tasks with Macros

Macros enable financial consultants to automate repetitive tasks and streamline workflows, saving valuable time and reducing the risk of errors. Grayson advises recording and editing macros to automate tasks such as data entry, formatting, and report generation, allowing consultants to focus on value-added activities.

6. Master Advanced Formulas and Functions

Excel's extensive library of formulas and functions offers endless possibilities for financial analysis and modeling. Grayson suggests mastering advanced formulas such as VLOOKUP, INDEX-MATCH, and array formulas to perform complex calculations, manipulate data, and create sophisticated models tailored to client needs.

7. Visualize Data with Charts and Graphs

Visualizing data is essential for conveying complex information in an easily digestible format. Excel offers a variety of chart types and customization options that enable consultants to create compelling visuals that resonate with clients. Grayson recommends experimenting with different chart styles to find the most effective way to present data and insights.

8. Collaborate and Share Workbooks Online

Excel's collaboration features enable financial consultants to work seamlessly with clients, colleagues, and stakeholders in real-time. Grayson highlights the benefits of sharing workbooks via OneDrive or SharePoint, allowing multiple users to collaborate on the same document, track changes, and maintain version control.

9. Protect Sensitive Data with Security Features

Data security is a top priority for financial consultants handling sensitive client information. Excel's built-in security features, such as password protection and encryption, help safeguard confidential data and ensure compliance with regulatory requirements. Grayson advises implementing security protocols to protect client data and maintain trust.

10. Stay Updated with Excel Training and Certification

Excel is a constantly evolving tool, with new features and updates released regularly. Grayson stresses the importance of staying updated with the latest Excel training and certification programs to enhance skills, explore new capabilities, and maintain proficiency in Excel's ever-changing landscape.

Elevating Client Services with Excel Mastery

Excel serves as a catalyst for innovation and excellence in financial consulting, empowering consultants to deliver exceptional services that add tangible value to clients. By implementing Grayson Garelick Excel tips, financial consultants can streamline workflows, enhance data analysis capabilities, and foster collaboration, ultimately driving client satisfaction and success. As financial consulting continues to evolve, mastering Excel remains a cornerstone of excellence, enabling consultants to thrive in a competitive landscape and exceed client expectations.

[wigglesforonce]

im having an absolute whale of a time rn. graphs are my calling

points for guessing what this mess is

[crewdlydrawn]

when I'm just walking around and something makes me screech to a halt and my eyeballs shoot out like binoculars and I gotta drop everything to take photos

[pythonjobsupport]

cfi excel crash course #SHORTS #excelcourse #macros #vba #excel #mis #msexcel #iptindia

Download Excel VBA MIS Learning – App – iOS … source

[willofthequill]

If Microsoft could stop just disappearing when it does an update that would be great!! The least it could do is give a fucking warning before it blows raspberries and closes everything out...

[maloubelou]

Hypericum by Andrew Kearton