Automating DingTalk Spreadsheets Using Python

This guide demonstartes how to interact with DingTalk's spreadsheet documents using Python. The process involves setting up a developer account, obtaining necessary crednetials, and implementing a Python class to perform various spreadsheet operations.

Prerequisites

1. Create a developer account on the DingTalk Open Platform
2. Register a new application and obtain your AppKey and AppSecret Request API permissions for document operations Installation

Install the required Python package:

pip install alibabacloud_dingtalk

Authentication Setup

Before working with documents, you need to obtain an access token. The following class handles token management with automatic refresh:

import os
import json
from datetime import datetime, timedelta
from alibabacloud_dingtalk.oauth2_1_0.client import Client as OAuthClient
from alibabacloud_dingtalk.oauth2_1_0 import models as oauth_models
from alibabacloud_tea_openapi import models as open_api_models

class DingTalkAuth:
    def __init__(self, app_key, app_secret, token_cache_path='./token_cache.json'):
        self.app_key = app_key
        self.app_secret = app_secret
        self.token_cache_path = token_cache_path
        self.client = self._create_client()
    
    def _create_client(self):
        config = open_api_models.Config()
        config.protocol = 'https'
        config.region_id = 'central'
        return OAuthClient(config)
    
    def _fetch_new_token(self):
        request = oauth_models.GetAccessTokenRequest(
            app_key=self.app_key,
            app_secret=self.app_secret
        )
        response = self.client.get_access_token(request)
        return response.body
    
    def _save_token_info(self, token_data):
        token_info = {
            'access_token': token_data.access_token,
            'expire_in': token_data.expire_in,
            'expire_at': (datetime.now() + timedelta(seconds=token_data.expire_in)).isoformat()
        }
        with open(self.token_cache_path, 'w', encoding='utf-8') as f:
            json.dump(token_info, f, indent=4)
        return token_data.access_token
    
    def _load_cached_token(self):
        if not os.path.exists(self.token_cache_path):
            return None
        
        with open(self.token_cache_path, 'r', encoding='utf-8') as f:
            token_info = json.load(f)
        
        expire_time = datetime.fromisoformat(token_info['expire_at'])
        if datetime.now() >= expire_time:
            return None
        
        return token_info['access_token']
    
    def get_access_token(self):
        cached_token = self._load_cached_token()
        if cached_token:
            return cached_token
        
        new_token = self._fetch_new_token()
        return self._save_token_info(new_token)

Spreadsheet Operations

The following class provides methods to interact with DingTalk spreadsheets:

from alibabacloud_dingtalk.doc_1_0.client import Client as DocClient
from alibabacloud_dingtalk.doc_1_0 import models as doc_models
from alibabacloud_tea_util import models as util_models

class DingTalkSpreadsheet:
    def __init__(self, access_token, workbook_id, operator_id):
        self.access_token = access_token
        self.workbook_id = workbook_id
        self.operator_id = operator_id
        self.client = self._create_client()
    
    def _create_client(self):
        config = open_api_models.Config()
        config.protocol = 'https'
        config.region_id = 'central'
        return DocClient(config)
    
    def _get_headers(self):
        headers = doc_models.GetAllSheetsHeaders()
        headers.x_acs_dingtalk_access_token = self.access_token
        return headers
    
    def list_sheets(self):
        headers = self._get_headers()
        request = doc_models.GetAllSheetsRequest(operator_id=self.operator_id)
        
        response = self.client.get_all_sheets_with_options(
            self.workbook_id, 
            request, 
            headers, 
            util_models.RuntimeOptions()
        )
        
        return [[sheet.id, sheet.name] for sheet in response.body.value]
    
    def create_sheet(self, sheet_name):
        if self._sheet_exists(sheet_name):
            return None
        
        headers = self._get_headers()
        request = doc_models.CreateSheetRequest(
            operator_id=self.operator_id,
            name=sheet_name
        )
        
        return self.client.create_sheet_with_options(
            self.workbook_id,
            request,
            headers,
            util_models.RuntimeOptions()
        )
    
    def _sheet_exists(self, sheet_name):
        sheets = self.list_sheets()
        return any(sheet_name == name for _, name in sheets)
    
    def get_sheet_id(self, sheet_name):
        sheets = self.list_sheets()
        for sheet_id, name in sheets:
            if name == sheet_name:
                return sheet_id
        return None
    
    def get_sheet_data(self, sheet_id=None, sheet_name=None):
        if sheet_id is None:
            sheet_id = self.get_sheet_id(sheet_name)
            if sheet_id is None:
                return None
        
        headers = self._get_headers()
        request = doc_models.GetSheetRequest(operator_id=self.operator_id)
        
        return self.client.get_sheet_with_options(
            self.workbook_id,
            sheet_id,
            request,
            headers,
            util_models.RuntimeOptions()
        )
    
    def update_cells(self, range_address, values, sheet_id=None, sheet_name=None, **format_options):
        if sheet_id is None:
            sheet_id = self.get_sheet_id(sheet_name)
            if sheet_id is None:
                return None
        
        headers = self._get_headers()
        request = doc_models.UpdateRangeRequest(
            operator_id=self.operator_id,
            values=values,
            **format_options
        )
        
        return self.client.update_range_with_options(
            self.workbook_id,
            sheet_id,
            range_address,
            request,
            headers,
            util_models.RuntimeOptions()
        )
    
    def clear_cell_data(self, range_address, sheet_id=None, sheet_name=None):
        if sheet_id is None:
            sheet_id = self.get_sheet_id(sheet_name)
            if sheet_id is None:
                return None
        
        headers = self._get_headers()
        request = doc_models.ClearDataRequest(operator_id=self.operator_id)
        
        return self.client.clear_data_with_options(
            self.workbook_id,
            sheet_id,
            range_address,
            request,
            headers,
            util_models.RuntimeOptions()
        )

Usage Example

Here's how to use the classes to automate spreadsheet operations:

# Configuration
APP_KEY = "your_app_key"
APP_SECRET = "your_app_secret"
WORKBOOK_ID = "your_workbook_id"
OPERATOR_ID = "your_operator_id"

# Initialize authentication
auth = DingTalkAuth(APP_KEY, APP_SECRET)
access_token = auth.get_access_token()

# Initialize spreadsheet client
spreadsheet = DingTalkSpreadsheet(access_token, WORKBOOK_ID, OPERATOR_ID)

# List all sheets
sheets = spreadsheet.list_sheets()
print("Available sheets:", sheets)

# Create a new sheet
new_sheet = spreadsheet.create_sheet("Automation Report")
if new_sheet:
    print("Sheet created with ID:", new_sheet.body.id)

# Update cells with data
data = [
    ["Product", "Sales", "Region"],
    ["Widget A", 1500, "North"],
    ["Widget B", 2300, "South"]
]

range_address = "A1:C3"
result = spreadsheet.update_cells(
    range_address, 
    data,
    sheet_name="Automation Report",
    number_format="#,##0"
)

if result:
    print("Cells updated successfully")

# Clear data from a range
clear_result = spreadsheet.clear_cell_data("A2:C3", sheet_name="Automation Report")
if clear_result:
    print("Data cleared successfully")

Cell Formatting Options

When updating cells, you can specify various formatting options:

Range Addressing

Range addresses follow the standard spreadsheet notation (e.g., "A1:C3" refers to cells from A1 to C3). When providing values for a range, use a 2D array where:

• Each inner array repersents a row
• Each element in the inner array represents a cell in that row

For example, to update the range "A1:B2" with values:

values = [
    ["Header 1", "Header 2"],
    ["Row 2, Col 1", "Row 2, Col 2"]
]