Skip to content

Instantly share code, notes, and snippets.

@rapatil

rapatil/Automating Salesforce Data Extraction Using Python.ipynb

Last active February 25, 2025 14:57
Download ZIP

Revisions

  1. rapatil revised this gist Aug 20, 2021. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions Automating Salesforce Data Extraction Using Python.ipynb
    View file
    Original file line number Diff line number Diff line change
    @@ -52,8 +52,8 @@
    "outputs": [],
    "source": [
    "# check all field names of the object\n",
    "desc=sf.UserInstall__c.describe()\n",
    "[field['name'] for field in desc['fields']]"
    "descri=sf.UserInstall__c.describe()\n",
    "[field['name'] for field in descri['fields']]"
    ]
    },
    {
  2. rapatil revised this gist Aug 16, 2021. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion Automating Salesforce Data Extraction Using Python.ipynb
    View file
    Original file line number Diff line number Diff line change
    @@ -80,7 +80,7 @@
    "metadata": {},
    "outputs": [],
    "source": [
    "# Generate a DataFrame from a dictionary\n",
    "# generate a DataFrame from a dictionary\n",
    "records = [dict(CreatedDate=rec['CreatedDate'], \n",
    " Record_Type=rec['Listing__r']['RecordTypeSnapshot__c'],\n",
    " ProviderName=rec['Listing__r']['ProviderName__c'], \n",
  3. rapatil revised this gist Aug 13, 2021. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion Automating Salesforce Data Extraction Using Python.ipynb
    View file
    Original file line number Diff line number Diff line change
    @@ -80,7 +80,7 @@
    "metadata": {},
    "outputs": [],
    "source": [
    "# read the results as a DataFrame\n",
    "# Generate a DataFrame from a dictionary\n",
    "records = [dict(CreatedDate=rec['CreatedDate'], \n",
    " Record_Type=rec['Listing__r']['RecordTypeSnapshot__c'],\n",
    " ProviderName=rec['Listing__r']['ProviderName__c'], \n",
  4. rapatil revised this gist Aug 11, 2021. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions Automating Salesforce Data Extraction Using Python.ipynb
    View file
    Original file line number Diff line number Diff line change
    @@ -97,7 +97,7 @@
    "# perform calculations and aggregate dataset\n",
    "df['CreatedDate']=pd.to_datetime(df['CreatedDate']).dt.to_period('M')\n",
    "df['bucket']=df['ProviderName'].apply(lambda x: 'Labs' if x in ('Salesforce','Salesforce Labs') else 'Other')\n",
    "df1=df.groupby(by=['CreatedDate','bucket'])['Name'].count().reset_index()"
    "df=df.groupby(by=['CreatedDate','bucket'])['Name'].count().reset_index()"
    ]
    },
    {
    @@ -141,7 +141,7 @@
    "outputs": [],
    "source": [
    "# paste the dataframe values into the respective GoogleSheet\n",
    "sh.worksheet_by_title('Sheet1').set_dataframe(df1,(1,1),copy_head=True)"
    "sh.worksheet_by_title('Sheet1').set_dataframe(df,(1,1),copy_head=True)"
    ]
    }
    ],
  5. rapatil revised this gist Aug 11, 2021. 1 changed file with 7 additions and 7 deletions.
    14 changes: 7 additions & 7 deletions Automating Salesforce Data Extraction Using Python.ipynb
    View file
    Original file line number Diff line number Diff line change
    @@ -36,13 +36,13 @@
    "outputs": [],
    "source": [
    "# download Salesforce report\n",
    "sf_org = 'https://oneappexchange.lightning.force.com/'# Salesforce Instance URL\n",
    "report_id = '12345' # add report id\n",
    "export_params = '?isdtp=p1&export=1&enc=UTF-8&xf=csv'\n",
    "sf_report_url = sf_org + report_id + export_params\n",
    "response = requests.get(sf_report_url, headers=sf.headers, cookies={'sid': sf.session_id})\n",
    "new_report = response.content.decode('utf-8')\n",
    "report_df = pd.read_csv(StringIO(new_report))"
    "sf_instance = 'https://oneappexchange.lightning.force.com/'# Salesforce Instance URL\n",
    "reportId = '12345' # add report id\n",
    "export = '?isdtp=p1&export=1&enc=UTF-8&xf=csv'\n",
    "sfUrl = sf_instance + reportId + export\n",
    "response = requests.get(sfUrl, headers=sf.headers, cookies={'sid': sf.session_id})\n",
    "download_report = response.content.decode('utf-8')\n",
    "df1 = pd.read_csv(StringIO(download_report))"
    ]
    },
    {
  6. rapatil revised this gist Aug 5, 2021. No changes.
  7. rapatil revised this gist Aug 5, 2021. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion Automating Salesforce Data Extraction Using Python.ipynb
    View file
    Original file line number Diff line number Diff line change
    @@ -131,7 +131,7 @@
    "outputs": [],
    "source": [
    "# clear all values of the sheet\n",
    "sh.worksheet_by_title('Sheet1').clear('B1','Z9999')"
    "sh.worksheet_by_title('Sheet1').clear('A1','Z9999')"
    ]
    },
    {
  8. rapatil created this gist Aug 5, 2021.
    169 changes: 169 additions & 0 deletions Automating Salesforce Data Extraction Using Python.ipynb
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,169 @@
    {
    "cells": [
    {
    "cell_type": "code",
    "execution_count": 47,
    "metadata": {},
    "outputs": [],
    "source": [
    "# import all packages\n",
    "from simple_salesforce import Salesforce\n",
    "import requests\n",
    "import pandas as pd\n",
    "from io import StringIO\n",
    "import httplib2\n",
    "import pygsheets"
    ]
    },
    {
    "cell_type": "code",
    "execution_count": 48,
    "metadata": {},
    "outputs": [],
    "source": [
    "# connect to Salesforce API using your credentials, you can use environment variables to protect your passwords\n",
    "sf = Salesforce(username='SALESFORCE_API_USER', \n",
    " password = 'SALESFORCE_API_PASSWORD',\n",
    " security_token='SALESFORCE_API_TOKEN',\n",
    "\n",
    " )"
    ]
    },
    {
    "cell_type": "code",
    "execution_count": 49,
    "metadata": {},
    "outputs": [],
    "source": [
    "# download Salesforce report\n",
    "sf_org = 'https://oneappexchange.lightning.force.com/'# Salesforce Instance URL\n",
    "report_id = '12345' # add report id\n",
    "export_params = '?isdtp=p1&export=1&enc=UTF-8&xf=csv'\n",
    "sf_report_url = sf_org + report_id + export_params\n",
    "response = requests.get(sf_report_url, headers=sf.headers, cookies={'sid': sf.session_id})\n",
    "new_report = response.content.decode('utf-8')\n",
    "report_df = pd.read_csv(StringIO(new_report))"
    ]
    },
    {
    "cell_type": "code",
    "execution_count": null,
    "metadata": {},
    "outputs": [],
    "source": [
    "# check all field names of the object\n",
    "desc=sf.UserInstall__c.describe()\n",
    "[field['name'] for field in desc['fields']]"
    ]
    },
    {
    "cell_type": "code",
    "execution_count": 51,
    "metadata": {},
    "outputs": [],
    "source": [
    "# writing SOQL query \n",
    "results=sf.query_all(\"\"\"\n",
    " Select \n",
    " CreatedDate,\n",
    " Listing__r.RecordTypeSnapshot__c,\n",
    " Name,\n",
    " Listing__r.ProviderName__c\n",
    " from UserInstall__c\n",
    " where CreatedDate=LAST_N_DAYS:7 \n",
    " \"\"\")"
    ]
    },
    {
    "cell_type": "code",
    "execution_count": 52,
    "metadata": {},
    "outputs": [],
    "source": [
    "# read the results as a DataFrame\n",
    "records = [dict(CreatedDate=rec['CreatedDate'], \n",
    " Record_Type=rec['Listing__r']['RecordTypeSnapshot__c'],\n",
    " ProviderName=rec['Listing__r']['ProviderName__c'], \n",
    " Name=rec['Name'] ) for rec in results['records']]\n",
    "df=pd.DataFrame(records) "
    ]
    },
    {
    "cell_type": "code",
    "execution_count": 53,
    "metadata": {},
    "outputs": [],
    "source": [
    "# perform calculations and aggregate dataset\n",
    "df['CreatedDate']=pd.to_datetime(df['CreatedDate']).dt.to_period('M')\n",
    "df['bucket']=df['ProviderName'].apply(lambda x: 'Labs' if x in ('Salesforce','Salesforce Labs') else 'Other')\n",
    "df1=df.groupby(by=['CreatedDate','bucket'])['Name'].count().reset_index()"
    ]
    },
    {
    "cell_type": "code",
    "execution_count": 54,
    "metadata": {},
    "outputs": [],
    "source": [
    "# connect to GoogleSheets API\n",
    "path_to_google_json = 'client_secret.json'\n",
    "http_client = httplib2.Http(timeout=100)\n",
    "retries = 10\n",
    "pygsheets.client.GOOGLE_SHEET_CELL_UPDATES_LIMIT = 40000\n",
    "gs = pygsheets.authorize(service_file = path_to_google_json,retries=retries,http_client=http_client)\n"
    ]
    },
    {
    "cell_type": "code",
    "execution_count": 55,
    "metadata": {},
    "outputs": [],
    "source": [
    "# open the Googlesheet file\n",
    "sh=gs.open('Test')"
    ]
    },
    {
    "cell_type": "code",
    "execution_count": 56,
    "metadata": {},
    "outputs": [],
    "source": [
    "# clear all values of the sheet\n",
    "sh.worksheet_by_title('Sheet1').clear('B1','Z9999')"
    ]
    },
    {
    "cell_type": "code",
    "execution_count": 57,
    "metadata": {},
    "outputs": [],
    "source": [
    "# paste the dataframe values into the respective GoogleSheet\n",
    "sh.worksheet_by_title('Sheet1').set_dataframe(df1,(1,1),copy_head=True)"
    ]
    }
    ],
    "metadata": {
    "kernelspec": {
    "display_name": "Python 3",
    "language": "python",
    "name": "python3"
    },
    "language_info": {
    "codemirror_mode": {
    "name": "ipython",
    "version": 3
    },
    "file_extension": ".py",
    "mimetype": "text/x-python",
    "name": "python",
    "nbconvert_exporter": "python",
    "pygments_lexer": "ipython3",
    "version": "3.8.3"
    }
    },
    "nbformat": 4,
    "nbformat_minor": 4
    }