如何用Apex从Salesforce Opportunity拉取数据并在赢单后推至谷歌表格

Got it, let's tackle your two Salesforce Apex requirements step by step—super common use cases, so I'll make this as actionable as possible.

Pulling data from Opportunity is straightforward with SOQL (Salesforce Object Query Language) embedded in Apex. Here's how to do it for both single and bulk scenarios:

单个Opportunity数据拉取 #

If you know the Opportunity ID (or another unique identifier), use a bound variable (safer than hardcoding to avoid injection) to query directly:

// 替换为实际的Opportunity ID
String targetOppId = '006XXXXXXXXXXXX'; 
// 按需选择需要的字段，关联对象字段用"关联对象.字段名"格式
Opportunity targetOpp = [
    SELECT Id, Name, AccountId, Account.Name, StageName, Amount
    FROM Opportunity
    WHERE Id = :targetOppId
    LIMIT 1
];

// 访问拉取到的数据
System.debug('Opportunity名称: ' + targetOpp.Name);
System.debug('关联客户名称: ' + targetOpp.Account.Name);

批量Opportunity数据拉取 #

For fetching multiple records (like all recently won opportunities), use a list query:

// 查询所有阶段为"已赢单关闭"的Opportunity，包含关联客户信息
List<Opportunity> wonOpps = [
    SELECT Id, Name, AccountId, Account.Name, StageName, Amount
    FROM Opportunity
    WHERE StageName = '已赢单关闭（Won Close）'
];

// 遍历处理每条记录
for(Opportunity opp : wonOpps) {
    System.debug('赢单Opportunity: ' + opp.Name + ', 对应客户: ' + opp.Account.Name);
}

关键注意事项 #

• Always use bound variables (like :targetOppId) in SOQL to prevent injection attacks.
• Only include fields you actually need—avoid SELECT * to stay within Salesforce governor limits and boost performance.
• If you need product-related data (product ID, price, discount), query the child OpportunityLineItem object:

  Opportunity oppWithProducts = [
      SELECT Id, Name, 
             (SELECT Product2Id, Product2.Name, UnitPrice, Discount FROM OpportunityLineItems)
      FROM Opportunity
      WHERE Id = :targetOppId
  ];
  

To automate this, you'll need an Apex Trigger (to detect the stage change) plus an Apex class to handle the Google Sheets API integration. Let's break this down:

Step 1: 创建Apex Trigger监听Opportunity更新 #

Use an after update trigger to catch records that just transitioned to the "已赢单关闭" stage:

trigger OpportunityWonTrigger on Opportunity (after update) {
    List<Opportunity> newlyWonOpps = new List<Opportunity>();
    
    // 过滤出阶段从非赢单状态变为已赢单关闭的记录
    for(Opportunity newOpp : Trigger.New) {
        Opportunity oldOpp = Trigger.OldMap.get(newOpp.Id);
        if(newOpp.StageName == '已赢单关闭（Won Close）' && oldOpp.StageName != newOpp.StageName) {
            newlyWonOpps.add(newOpp);
        }
    }
    
    // 如果有符合条件的记录，调用推送服务
    if(!newlyWonOpps.isEmpty()) {
        GoogleSheetsPushService.pushWonOppData(newlyWonOpps);
    }
}

Step 2: 创建Apex类处理谷歌表格API推送 #

First, set up a Named Credential in Salesforce to store your Google Sheets API credentials (never hardcode tokens/secrets!). Then use this service class:

public class GoogleSheetsPushService {
    // 替换为你的谷歌表格ID和目标工作表名称
    private static final String SHEET_ID = 'your-google-sheet-id';
    private static final String SHEET_NAME = 'WonOpportunityRecords';
    
    public static void pushWonOppData(List<Opportunity> wonOpps) {
        // 查询Opportunity关联的产品明细（OpportunityLineItem）
        Map<Id, Opportunity> oppWithLineItems = new Map<Id, Opportunity>([
            SELECT Id, Account.Name, AccountId,
                   (SELECT Product2.Name, Product2Id, UnitPrice, Discount FROM OpportunityLineItems)
            FROM Opportunity
            WHERE Id IN :wonOpps
        ]);
        
        // 构造谷歌表格需要的行数据，先加表头
        List<List<Object>> sheetRows = new List<List<Object>>();
        sheetRows.add(new List<Object>{'客户名称', '客户ID', '产品名称', '产品ID', '单价', '折扣'});
        
        // 遍历每个赢单Opportunity的产品明细，组装行数据
        for(Opportunity opp : oppWithLineItems.values()) {
            for(OpportunityLineItem oli : opp.OpportunityLineItems) {
                sheetRows.add(new List<Object>{
                    opp.Account.Name,
                    opp.AccountId,
                    oli.Product2.Name,
                    oli.Product2Id,
                    oli.UnitPrice,
                    oli.Discount
                });
            }
        }
        
        // 构造API请求体
        String requestBody = JSON.serialize(new Map<String, Object>{
            'values' => sheetRows
        });
        
        // 调用谷歌表格API（使用Named Credential做认证）
        HttpRequest req = new HttpRequest();
        req.setEndpoint('callout:GoogleSheetsAPI/v4/spreadsheets/' + SHEET_ID + '/values/' + SHEET_NAME + ':append?valueInputOption=USER_ENTERED');
        req.setMethod('POST');
        req.setHeader('Content-Type', 'application/json');
        req.setBody(requestBody);
        
        Http http = new Http();
        HttpResponse res = http.send(req);
        
        // 处理响应，可根据需要添加错误日志或重试逻辑
        if(res.getStatusCode() != 200) {
            System.debug('数据推送失败: ' + res.getBody());
            // 可选：将错误记录到自定义对象或抛出异常
        } else {
            System.debug('赢单数据已成功推送至谷歌表格');
        }
    }
}

关键配置与注意事项 #

• Named Credential Setup: Go to Salesforce Setup → Named Credentials, create a new one for Google Sheets API. Use OAuth 2.0 to authenticate with Google, ensuring your org has the necessary API permissions.
• Google Sheets API Enablement: Make sure the Google Sheets API is enabled in your Google Cloud Console, and the service account has edit access to the target spreadsheet.
• Governor Limits: For bulk updates, use Batch Apex or Queueable Apex to avoid hitting Salesforce API call limits.
• Error Handling: Add retry logic or error logging (e.g., to a custom Log object) for cases where the Google API call fails.

内容的提问来源于stack exchange，提问作者m-imran