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
}

Thank you very much, quite eye opening. Now with multi-factor authentication (MFA), the code fails to authenticate into Salesforce. What changes are required in the code to take care of MFA.

Thank you very much, quite eye opening. Now with multi-factor authentication (MFA), the code fails to authenticate into Salesforce. What changes are required in the code to take care of MFA.

Please work with your Salesforce Admin for permission settings and MFA. The code works on my end.

using your code pretty much verbatim for downloading a salesforce report I am getting a error thrown in requests: requests.exceptions.TooManyRedirects: Exceeded 30 redirects.

Do you know if something has changed in the past couple months that would be causing so many redirects? If I copy sfUrl into a browser (that is also logged in) it does generate and download the report as expected...

The script is running successfully on our end. Please cross-check the salesforce instance name/url with your Salesforce Admin.

Thank you so much for your code to get report from salesforce, How can i apply filter on report to get only specific data.

using your code pretty much verbatim for downloading a salesforce report I am getting a error thrown in requests: requests.exceptions.TooManyRedirects: Exceeded 30 redirects.

Do you know if something has changed in the past couple months that would be causing so many redirects? If I copy sfUrl into a browser (that is also logged in) it does generate and download the report as expected...

I am also experiencing this redirect issue. Any one come up with a solution?

Copying sfUrl and pasting into browser works.

In my case only report's header is being downloaded without any data rows (in both the browser and the library); how to overcome this limitation?

In my case only report's header is being downloaded without any data rows (in both the browser and the library); how to overcome this limitation?

Please open up your report and remove filters and save it then try

It was created by business person, with filters and grouping; I cannot alter it as it will become invalid

using your code pretty much verbatim for downloading a salesforce report I am getting a error thrown in requests: requests.exceptions.TooManyRedirects: Exceeded 30 redirects.
Do you know if something has changed in the past couple months that would be causing so many redirects? If I copy sfUrl into a browser (that is also logged in) it does generate and download the report as expected...

I am also experiencing this redirect issue. Any one come up with a solution?

Copying sfUrl and pasting into browser works.

facing the same issue. any one knows how to deal with this?

update: try updating lightning.force to my.salesforce

using your code pretty much verbatim for downloading a salesforce report I am getting a error thrown in requests: requests.exceptions.TooManyRedirects: Exceeded 30 redirects.
Do you know if something has changed in the past couple months that would be causing so many redirects? If I copy sfUrl into a browser (that is also logged in) it does generate and download the report as expected...

I am also experiencing this redirect issue. Any one come up with a solution?
Copying sfUrl and pasting into browser works.

facing the same issue. any one knows how to deal with this?

> update: try updating lightning.force to my.salesforce

Yep! This is what worked for me!