Gws Israeli Business Sheets

GWS Israeli Business Sheets

Instructions

Step 1: Verify GWS CLI Installation and Authentication

Before performing any Google Sheets operations, confirm the Google Workspace CLI is installed and authenticated.

# Check if gws is installed
gws --version

# If not installed, install globally
npm install -g @google/gws

# Authenticate with Google OAuth
gws auth login

# Verify authentication status
gws auth status

If the user has not configured a Google Cloud project, guide them through gws auth setup to create OAuth credentials.

Step 2: Create a New Financial Tracking Spreadsheet

When the user wants to set up a new income/expense tracking sheet, create it with proper Israeli financial structure.

Sheet structure for Israeli freelancers:

Tax-deductible categories for Israeli businesses:

To create the spreadsheet with headers:

# Create a new spreadsheet (returns spreadsheet ID)
gws sheets create --title "Business Tracker 2026"

# Set up headers in the first row
gws sheets append --spreadsheet-id SPREADSHEET_ID --range "Sheet1!A1:J1" \
  --values '[["Date","Description","Category","Amount (excl. VAT)","VAT (18%)","Total (incl. VAT)","Type","Invoice #","Payment Method","Notes"]]'

Step 3: Append Income and Expense Entries

When the user wants to log a transaction, calculate the VAT automatically and append the row.

For income entries (user received payment):

# Calculate: if user received 5,900 ILS total, the breakdown is:
# Amount excl. VAT = Total / 1.18 = 5,000 ILS
# VAT = Amount * 0.18 = 900 ILS
gws sheets append --spreadsheet-id SPREADSHEET_ID --range "Sheet1!A:J" \
  --values '[["15/01/2026","Web Development Project","Professional Services","5000","900","5900","Income","INV-2026-001","Bank Transfer",""]]'

For expense entries:

# Example: Office internet bill of 236 ILS (200 + 36 VAT)
gws sheets append --spreadsheet-id SPREADSHEET_ID --range "Sheet1!A:J" \
  --values '[["20/01/2026","Bezeq Internet","Phone & Internet","200","36","236","Expense","","Direct Debit",""]]'

VAT calculation formulas:

Step 4: Read and Summarize Financial Data

When the user needs a financial overview, read the data and compute summaries.

# Read all entries from the sheet
gws sheets read --spreadsheet-id SPREADSHEET_ID --range "Sheet1!A:J"

# Read entries with JSON output for programmatic processing
gws sheets read --spreadsheet-id SPREADSHEET_ID --range "Sheet1!A:J" --output json

After reading the data, calculate and present:

• Total income for the period
• Total expenses for the period
• Net profit (income minus expenses)
• Total VAT collected (on income)
• Total VAT paid (on expenses, input VAT)
• VAT liability (collected minus paid, amount to report to tax authority)

Bi-monthly VAT reporting periods (Israel):

Step 5: Generate Tax-Period Summary Reports

When the user needs to prepare data for their accountant or for VAT reporting, create a summary sheet.

# Read all data
gws sheets read --spreadsheet-id SPREADSHEET_ID --range "Sheet1!A:J" --output json

After reading, use Python (via scripts/vat-summary.py) to:

1. Filter transactions by the bi-monthly period
2. Group by income vs. expenses
3. Calculate total VAT collected and input VAT
4. Generate a summary suitable for the accountant

Then write the summary to a new tab:

# Create summary headers in a new sheet tab
gws sheets append --spreadsheet-id SPREADSHEET_ID --range "VAT-Period-1!A1:D1" \
  --values '[["Category","Total Amount","Total VAT","Transaction Count"]]'

# Append summary rows
gws sheets append --spreadsheet-id SPREADSHEET_ID --range "VAT-Period-1!A:D" \
  --values '[["Total Income","50000","9000","15"],["Total Expenses","20000","3600","25"],["VAT Liability","","5400",""],["Net Profit","30000","",""]]'

Step 6: Backup Sheets as CSV

When the user wants to create local backups or share data with their accountant, export to CSV.

# Export the main tracking sheet as CSV
gws sheets read --spreadsheet-id SPREADSHEET_ID --range "Sheet1!A:J" --output csv > business-tracker-2026.csv

# Export a specific VAT period
gws sheets read --spreadsheet-id SPREADSHEET_ID --range "VAT-Period-1!A:D" --output csv > vat-period-1-2026.csv

Use the scripts/backup-sheets.py script for automated multi-sheet backup:

python scripts/backup-sheets.py --spreadsheet-id SPREADSHEET_ID --output-dir ./backups/2026-01

Step 7: Auto-Log Payments from Structured Input

When the user provides transaction data in bulk (from a bank statement or invoice list), parse and append multiple entries at once.

# Append multiple rows in one call
gws sheets append --spreadsheet-id SPREADSHEET_ID --range "Sheet1!A:J" \
  --values '[
    ["01/02/2026","Client A - Monthly Retainer","Professional Services","10000","1800","11800","Income","INV-2026-010","Bank Transfer",""],
    ["03/02/2026","AWS Hosting","Software & Subscriptions","450","81","531","Expense","","Credit Card",""],
    ["05/02/2026","Business Lunch - Client B","Meals & Entertainment","300","54","354","Expense","","Credit Card","80% deductible"]
  ]'

Step 8: Use Dry-Run Mode for Validation

Before making changes, always offer the user a dry-run preview.

# Preview what would be appended without writing
gws sheets append --spreadsheet-id SPREADSHEET_ID --range "Sheet1!A:J" \
  --values '[["15/03/2026","Test Entry","Office Rent","5000","900","5900","Expense","","Bank Transfer",""]]' \
  --dry-run

Examples

Example 1: Israeli Freelancer Sets Up Monthly Tracking

User says: "Create a Google Sheet to track my freelance income and expenses with VAT"

Actions:

1. Run gws sheets create --title "Freelance Tracker 2026" to create the spreadsheet
2. Append header row with all 10 columns (Date through Notes)
3. Show the user the spreadsheet ID and link
4. Explain the column structure and how VAT will be calculated for each entry

Result: A new Google Sheet with proper Israeli freelancer financial structure, ready for entries.

Example 2: Generate Bi-Monthly VAT Summary for Accountant

User says: "Create a VAT summary for January-February 2026 and export it as CSV"

Actions:

1. Run gws sheets read to pull all entries from the tracking sheet
2. Run python scripts/vat-summary.py to filter Jan-Feb transactions and compute totals
3. Write summary to a new "VAT-Period-1-2026" tab in the spreadsheet
4. Export the summary tab as CSV with gws sheets read --output csv
5. Display the summary: total income, total expenses, VAT collected, input VAT, net VAT liability

Result: A clean VAT period summary both in the Google Sheet and as a local CSV file ready to send to the accountant.

Example 3: Auto-Log Bank Transfers into Expense Sheet

User says: "I got these payments this month: Client A paid 11,800 for consulting, I paid 531 for hosting, and 354 for a business lunch"

Actions:

1. Parse each transaction, calculate VAT breakdown (divide totals by 1.18)
2. Categorize: consulting = Professional Services (income), hosting = Software & Subscriptions (expense), lunch = Meals & Entertainment (expense, 80% deductible)
3. Use gws sheets append with multi-row values array
4. Confirm all entries were logged with correct VAT calculations

Result: Three new rows appended to the tracking sheet with proper categorization, VAT breakdown, and deductibility notes.

Bundled Resources

Scripts

• scripts/vat-summary.py -- Generate bi-monthly VAT summary reports from sheet data. Run: python scripts/vat-summary.py --help
• scripts/backup-sheets.py -- Backup Google Sheets tabs as local CSV files. Run: python scripts/backup-sheets.py --help

References

• references/israeli-tax-categories.md -- Complete list of Israeli tax-deductible expense categories with deduction rates. Consult when categorizing a business expense.
• references/gws-sheets-recipes.md -- Common gws CLI recipes for Google Sheets operations. Consult when performing sheet operations beyond basic read/append.

Gotchas

• Agents often use the old 17% VAT rate in calculations. The current Israeli VAT rate is 18% (effective January 2025). All VAT formulas must use 0.18, not 0.17.
• Israeli date format is DD/MM/YYYY, not MM/DD/YYYY. Agents default to US date format, which causes incorrect date entries (e.g., 03/01/2026 means January 3rd in Israel, not March 1st).
• The Google Workspace CLI (gws) requires server-side OAuth authentication. Agents may try to use API keys or service accounts, which are not supported by gws auth login.
• Agents may categorize car expenses as 100% deductible, but Israeli tax law limits car expense deductions to 45% or a fixed amount. This is a common mistake in expense categorization.

Troubleshooting

Error: "gws: command not found"

Cause: The Google Workspace CLI is not installed or not in PATH. Solution: Install with npm install -g @google/gws. If using npx, prefix commands with npx @google/gws.

Error: "Authentication required" or "Token expired"

Cause: The user has not authenticated or the OAuth token has expired. Solution: Run gws auth login to re-authenticate. If the Google Cloud project is not configured, run gws auth setup first.

Error: "Spreadsheet not found" or "404"

Cause: The spreadsheet ID is incorrect or the user does not have access. Solution: Verify the spreadsheet ID from the Google Sheets URL (the string between /d/ and /edit). Ensure the authenticated Google account has edit access to the sheet.

Error: "VAT calculation mismatch"

Cause: Rounding differences between manual calculation and sheet formulas. Solution: Always round VAT to 2 decimal places. Use the formula: Math.round(amount * 18) / 100 for precise Shekel calculations. Israeli tax authority accepts rounding to the nearest agora.