We want to Synchronize Google Sheet with ERP Next so we can fetch data from google sheet to ERP Next ( for Stock Entry - date and item wise )
Syncing between very different systems is always a bit messy, but if you really need to do it this way you might use a middleware system like n8n. It has nodes for both google sheets and the ERPNext REST API.
Closed… Coding Done successfully.
It would be very helpful to the community if you shared how you did it!
2 Likes
Love to Share the Steps :
Step 1 : visit: console.cloud.google.com/ and Create a New Project and Select the Project.
Step 2 : In Search Box : type > GOOGLE DRIVE API, open and Enable
Step 3 : Same as above open GOOGLE SHEET API and Enable
Step 4 : Go to home page, Select Project → Dashboard → APIs and Services → Credentials → Create Credentials → Select Service Account to create.
Step 5 : Add a Service account Name, which will also create a service account ID, which we will use for this service.
Step 6 : Create and Continue → then Done
Step 7 : Click on Service Account ID ( in Credentials ) will redirect to IAM & Admin Page.
Step 8 : Click ok “KEYS” tab → Add Keys → Create New Keys → JSON → Create → Save the Downloaded Json File.
Step 9 : Open Json File and Copy ID eg : “client_email”: “yourId@YourProject.iam.gserviceaccount.com”, Id will use for Share the Resource (like Google Sheet).
Step 10 : Go to Desired Sheet → Click on Share → Add Copied email Id → Share.
Step 11 : Download and Install APIs to Your System : Eg for Ubuntu : pip install gspread or install gspread
Step 12 : Add: “gspread” to your Frappe Custom App’s requirment.txt file. and run “bench update --requirement” on console / terminal
Google Sheet
Abc.py
import frappe
from frappe.model.document import Document
from google.oauth2.service_account import Credentials
import gspread
data from DOwnloaded Json File from Step 8 - Just copy and Paste Full data below
credentials = {
“type”: “service_account”,
“project_id”: “your”,
“private_key_id”: “d1…56”,
“private_key”: “-----BEGIN PRIVATE KEY-----\nMIIE…sF2F+Vg==\n-----END PRIVATE KEY-----\n”,
“client_email”: “your_id@your_project.iam.gserviceaccount.com”,
“client_id”: “1**********0”,
“auth_uri”: “Sign in – Google accounts”,
“token_uri”: “https://oauth2.googleapis.com/token”,
“auth_provider_x509_cert_url”: “https://www.googleapis.com/oauth2/v1/certs”,
“client_x509_cert_url”: “https://www.googleapis.com/robot/v1/m***************iceaccount.com”
}
gc = gspread.service_account_from_dict(credentials)
sh = gc.open(“Your Google Sheet File Name”)
wks = sh.worksheet(“worksheet-1”) # for Worksheet Name
items = wks.get_all_records() # will import all data from worksheet
for i in items:
print(i[‘buyer’], i[‘item_code’], i[‘item_name’], i[‘in_01_04’], i[‘out_01_04’]) # print on Console
class Production(Document):
pass
3 Likes
hi atul, thanks for the response, i had a few questions like where do add the python file.