Google Sheets Functions

  1. Home
  2. Docs
  3. Google Sheets Functions
  4. GORILLA_FINANCES() – Get sales, charges and fees for any period using payments report data

GORILLA_FINANCES() – Get sales, charges and fees for any period using payments report data

Download sample FINANCES spreadsheet with formulas ready to go.

Sample Usage

=GORILLA_FINANCES("Custom", "Selling price (Principal)", "shipment", "US", "SKU12345", "2021-01-01", "2021-03-16")
=GORILLA_FINANCES("Last Year", A1:A13)
=GORILLA_FINANCES(A1:A5, A2:A12, "adjustment", "US")

Syntax Description

Description:

Get inventory movement status of a product such as how many is in stock, transfer or inbound.

Syntax:

GORILLA_FINANCES(period, type, [event], [marketplace], [sku], [start_date], [end_date])

for Gorilla Agency, the syntax is

GORILLA_FINANCES(sellerID, period, type, [event], [marketplace], [sku], [start_date], [end_date])

period – REQUIRED

Predefined period to get sales. See full list of predefined periods below.

Can be one value or an array of values. If the period array is vertical (on the left as a column), then the “type” parameter must be a horizontal array or string (as a header up the top).

type – REQUIRED

List of Charges/Fees to get the values for. Data is grouped by fee/charge type. Can be a string or an array. Has to be valid fee names (See function GORILLA_FEETYPE() and GORILLA_CHARGETYPE() to get a list of them). If type array is vertical, period has to be horizontal or a string.

event – OPTIONAL

Filter finance results for a specific event type.

Valid event values are:

  • Shipment
  • Refund
  • GuaranteeClaim
  • Chargeback
  • CouponPayment
  • Adjustment
  • Other

marketplace – OPTIONAL

Marketplace filter. Use All for all marketplaces and Default for the seller default. Other valid values are country codes US, IT, etc. or Amazon Marketplace ID like ATVPDKIKX0DER.

sku – OPTIONAL

SKU or ASIN of the product. Only one SKU or ASIN is supported. This function does NOT support arrays for SKU or ASIN input.

start_date – OPTIONAL

Starting date to get data when period attribute is “custom”.

end_date – OPTIONAL

Ending date to get data when period attribute is “custom”.

Examples:

Use ranges (e.g.A2:A100) in your formula wherever possible for quicker results and to prevent Google blocking your account for 24 hrs due to overloading their resources.

=GORILLA_FINANCES(“Custom”, “Selling price (Principal)”, “shipment”, “US”, “SKU12345”, “2021-01-01”, “2021-03-16”)

The full formula in action.

Gets the “Selling Price (Principal)” from the “shipment” event in the US market, for SKU1234 between 2021-01-01 and 2021-03-16.

=GORILLA_FINANCES(“Last Year”, A1:A13)

where A1:A13 is

  • Selling price (Principal)
  • Selling price (Principal non-FBA)
  • Shipping charge
  • Gift wrap charge
  • Tax collected by the seller
  • Tax collected by the seller on a Shipping Charge
  • Tax collected by the seller on a Gift wrap charge
  • Seller & Amazon Promos
  • Tax withheld by Amazon on the Principal
  • Tax withheld by Amazon on the Shipping Charge
  • Tax withheld by Amazon on other miscellaneous charges
  • LowValueGoodsTax-Principal
  • LowValueGoodsTax-Shipping

Displays the consolidated numbers for the transaction “type” listed above for “last year”.

=GORILLA_FINANCES(A1:A5, A2:A12, “adjustment”, “US”)

where A1:A5 is

  • 2021-01
  • 2021-02
  • 2021-03
  • 2021-04
  • 2021-05

where A1:A12 is

  • WAREHOUSE_DAMAGE
  • WAREHOUSE_LOST
  • REVERSAL_REIMBURSEMENT
  • MISSING_FROM_INBOUND
  • CS_ERROR_ITEMS
  • FREE_REPLACEMENT_REFUND_ITEMS
  • COMPENSATED_CLAWBACK
  • WAREHOUSE_LOST_MANUAL
  • MISSING_FROM_INBOUND_CLAWBACK
  • INCORRECT_FEES_ITEMS
  • WAREHOUSE_DAMAGE_EXCEPTION
  • MULTICHANNEL_ORDER_LOST

This formula will pull data for the 5 monthly time periods as well as the adjustment transactions for the US market.

Period Attributes:

    • Today
    • Yesterday
  • Same day last week
  • Same day last month
  • Same day last year
  • Last 7 Days
  • Last 14 Days
  • Last 30 Days
  • This Week
  • This Month
  • This Quarter
  • This Year
  • This Year-to-Last-Month
  • Last Week
  • Last Month
  • Last Quarter
  • Last Year
  • Last 60 Days
  • YYYY
  • Last 12 Months
  • Last 7 Days Week Ago
  • Last 7 Days Year Ago
  • Last 30 Days Month Ago
  • Last 30 Days Year Ago
  • Last Week Year Ago
  • Last Month Year Ago
  • YYYY-MM
  • YYYYQ1… YYYYQ4

Type Attributes:

Due to the ever increasing and changing list of charges and fees by Amazon, get the latest and full list by entering:

=GORILLA_CHARGETYPE( , , , “YES”)
=GORILLA_FEETYPE( , , , “YES”)

for agency users

=GORILLA_CHARGETYPE(“sellerID”, , , , “YES”)
=GORILLA_FEETYPE(“sellerID”, , , , “YES”)

Event Attributes:

  • Shipment
  • Refund
  • GuaranteeClaim
  • Chargeback
  • CouponPayment
  • Adjustment
  • Other

For Gorilla Agency, the formulas will start with the seller ID:

=GORILLA_FINANCES("SellerID", "Custom", "Selling price (Principal)", "shipment", "US", "SKU12345", "2021-01-01", "2021-03-16")
=GORILLA_FINANCES("SellerID", "Last Year", A1:A13)
=GORILLA_FINANCES("SellerID", A1:A5, A2:A12, "adjustment", "US")

How can we help?