Approach: Automating Salesforce Data Extraction Using Python

Automating Salesforce Data Extraction Using Python.ipynb

{
 "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_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))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# check all field names of the object\n",
    "descri=sf.UserInstall__c.describe()\n",
    "[field['name'] for field in descri['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": [
    "# 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",
    "                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",
    "df=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('A1','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(df,(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
}