通过Cloud Function触发Apps Script无日志问题求助

配置Cloud Function关联存储桶，新文件存入时触发函数，流程为：调用Document AI提取文件数据→保存至BigQuery→HTTP请求触发Apps Script。Cloud Function日志显示Google Apps Script Response Status Code: 200，但Apps Script无任何日志记录，手动测试Apps Script可正常运行。

Cloud Function 代码 #

import os
from google.api_core.client_options import ClientOptions
from google.cloud import bigquery
from google.cloud import documentai
from google.cloud import storage
import json
import requests

project_id = 'myproject001'
location = 'eu'
mime_type = 'application/pdf'
processor_id = '111aaaasdfvgetrt11'
bucket_name = 'p2p_docai_extraction'
dataset_id = 'p2p_docai_data'
table_id = 'invoices_data'
apps_script_url = 'https://script.google.com/a/macros/carrefour.com/s/zsdcvffxNPdscvbnnzo3z27CT1AA3Ocgnru7Pgl_ujikklmnfr-Mg/exec'

def trigger_apps_script(file_name):
    # Include the file name as a query parameter in the URL
    apps_script_url_with_params = f'{apps_script_url}&file_name={file_name}'

    try:
        # Send an HTTP GET request to trigger the Google Apps Script function
        response = requests.get(apps_script_url_with_params)
        print('Google Apps Script Response Content:', response.text)  # Log the response content
        print('Google Apps Script Response Status Code:', response.status_code)  # Log the response status code

        if response.status_code == 200:
            print('Google Apps Script executed successfully')
        else:
            print('Error executing Google Apps Script:', response.status_code)
    except Exception as e:
        print('Error executing Google Apps Script:', str(e))

def parse_to_bigquery(event, context):  # Level 0
    file = event  # Level 1
    print(f"Processing file: {file['name']}.")  # Level 1

    local_path = "/tmp/" + file['name']  # Level 1

    storage_client = storage.Client()  # Level 1
    bucket = storage_client.bucket(bucket_name)  # Level 1
    blob = bucket.blob(file['name'])  # Level 1
    blob.download_to_filename(local_path)  # Level 1

    opts = ClientOptions(api_endpoint=f"{location}-documentai.googleapis.com")  # Level 1
    client = documentai.DocumentProcessorServiceClient(client_options=opts)  # Level 1
    name = client.processor_path(project_id, location, processor_id)   # Level 1
    
    with open(local_path, "rb") as image:   # Level 1
        image_content = image.read()   # Level 2
    
    raw_document = documentai.RawDocument(content=image_content, mime_type=mime_type)   # Level 1
    request = documentai.ProcessRequest(name=name, raw_document=raw_document)   # Level 1
    result = client.process_document(request=request)   # Level 1
    document = result.document   # Level 1

    row_to_bq = {
        "file": file['name'],
        "amount_due": None,
        "amount_dueConfidence": None,
        "amount_paid_since_last_invoice": None,
        "amount_paid_since_last_invoiceConfidence": None,
        "carrier": None,
        "carrierConfidence": None,
        "currency": None,
        "currencyConfidence": None,
        "currency_exchange_rate": None,
        "currency_exchange_rateConfidence": None,
        "customer_tax_id": None,
        "customer_tax_idConfidence": None,
        "delivery_date": None,
        "delivery_dateConfidence": None,
        "due_date": None,
        "due_dateConfidence": None,
        "freight_amount": None,
        "freight_amountConfidence": None,
        "invoice_date": None,
        "invoice_dateConfidence": None,
        "invoice_id": None,  
        "invoice_idConfidence": None,
        "invoice_type": None,
        "invoice_typeConfidence": None,
        "line_item": None,
        "line_itemConfidence": None,
        "net_amount": None,
        "net_amountConfidence": None,
        "payment_terms": None,
        "payment_termsConfidence": None,
        "purchase_order": None,
        "purchase_orderConfidence": None,
        "receiver_address": None,
        "receiver_addressConfidence": None,
        "receiver_email": None,
        "receiver_emailConfidence": None,
        "receiver_name": None,
        "receiver_nameConfidence": None,
        "receiver_phone": None,
        "receiver_phoneConfidence": None,
        "receiver_tax_id": None,
        "receiver_tax_idConfidence": None,
        "receiver_website": None,
        "receiver_websiteConfidence": None,
        "remit_to_address": None,
        "remit_to_addressConfidence": None,
        "remit_to_name": None,
        "remit_to_nameConfidence": None,
        "ship_from_address": None,
        "ship_from_addressConfidence": None,
        "ship_from_name": None,
        "ship_from_nameConfidence": None,
        "ship_to_address": None,
        "ship_to_addressConfidence": None,
        "ship_to_name": None,
        "ship_to_nameConfidence": None,
        "supplier_address": None,
        "supplier_addressConfidence": None,
        "supplier_email": None,
        "supplier_emailConfidence": None,
        "supplier_iban": None,
        "supplier_ibanConfidence": None,
        "supplier_name": None,
        "supplier_nameConfidence": None,
        "supplier_payment_ref": None,
        "supplier_payment_refConfidence": None,
        "supplier_phone": None,
        "supplier_phoneConfidence": None,
        "supplier_registration": None,
        "supplier_registrationConfidence": None,
        "supplier_tax_id": None,
        "supplier_tax_idConfidence": None,
        "total_amount": None,
        "total_amountConfidence": None,
        "total_tax_amount": None,
        "total_tax_amountConfidence": None,
        "vat": None,
        "vatConfidence": None,
        "amount": None,
        "amountConfidence": None,
        "category_code": None,
        "category_codeConfidence": None,
        "tax_amount": None,
        "tax_amountConfidence": None,
        "tax_rate": None,
        "tax_rateConfidence": None,
    }   # Level 1

    entity_row = []   # Level 1
    line = []   # Level 1
    for entity in document.entities:   # Level 1
        if entity.type_ == "line_item":   # Level 2
            item = {   # Level ...
                "name": entity.type_, 
                "content": entity.mention_text,   
                "confidence": f"{entity.confidence:.0%}"   
            }   
            line.append(item)   # ...2
        row_to_bq[entity.type_] = entity.mention_text   
        row_to_bq[entity.type + 'Confidence'] = f"{entity.confidence:.0%}"   

    row_to_bq['line_item'] = str(line)   # ...2
    
    client = bigquery.Client()   # ...2
    table_ref = client.dataset(dataset_id).table(table_id)   
    table = client.get_table(table_ref)   # ...2

    rows_to_insert = [row_to_bq]   # ...2

    errors = client.insert_rows_json(table, rows_to_insert)  
    trigger_apps_script(file['name'])   # ...2 (Call to the function defined above)
    return 'Processing completed', 200

Apps Script 代码 #

function doGet(e) {
  // Use getParameters to extract query parameters
  var file_name = e.parameter.file_name;
  var invoice_id = e.parameter.invoice_id || null;
  Logger.log("Received parameters:");
  Logger.log("file_name: " + file_name);
  Logger.log("invoice_id: " + invoice_id);

  // Fetch data from BigQuery
  var projectId = 'myproject001';
  var datasetId = 'p2p_docai_data';
  var tableId = 'invoices_data';

  // Encode the file_name parameter to make it safe for the SQL query
  var encodedFileName = encodeURIComponent(file_name);

  var query = 'SELECT ' +
    'file, invoice_id, invoice_date, net_amount, total_amount, receiver_address, receiver_name, ' +
    'receiver_phone, supplier_name, supplier_address, supplier_email, delivery_date, supplier_iban, ' +
    'total_tax_amount, currency, category_code, ' +
    'JSON_EXTRACT_SCALAR(line_item, \'$.unit\') AS unit, ' +
    'JSON_EXTRACT_SCALAR(line_item, \'$.content\') AS description ' +
    'FROM `' + projectId + '.' + datasetId + '.' + tableId + '`, ' +
    'UNNEST(JSON_EXTRACT_ARRAY(line_item, \'$\')) AS item ' +
    'WHERE file = "' + encodedFileName + '"' +
    (invoice_id ? ' AND invoice_id = "' + invoice_id + '"' : '') +
    'GROUP BY file, invoice_id, invoice_date, net_amount, description, receiver_address, receiver_name, ' +
    'receiver_phone, supplier_address, supplier_email, supplier_name, unit, delivery_date, supplier_iban, total_amount, ' +
    'total_tax_amount, currency, category_code';

  var request = {
    query: query,
    useLegacySql: false
  };

  var queryResults = BigQuery.Jobs.query(request, projectId);
  var data = queryResults.rows;

  if (!data || data.length === 0) {
    return ContentService.createTextOutput("No data found for the given parameters.");
  }

  // Open the template Google Sheet
  var templateSpreadsheet = SpreadsheetApp.openById('1AaajZuGdddddddddddQuy0U_jhkPc4');
  var newSpreadsheet = templateSpreadsheet.copy(file_name);
  newSpreadsheet.setName(file_name);

  // Open the second sheet in the new spreadsheet
  var sheet2 = newSpreadsheet.getSheets()[1]; // Index 0 is the first sheet

  // Populate specific cells in page 2 with data from the query
  for (var i = 0; i < data.length; i++) {
    var rowData = data[i].f;
    var rowIndex = i + 7;
      Logger.log("Row " + rowIndex + ":");
      Logger.log("receiver_name: " + decodeURIComponent(rowData[6].v));
      Logger.log("delivery_date: " + decodeURIComponent(rowData[10].v));

    // Populate specific cells in page 2 with data from the query result
    sheet2.getRange('C' + rowIndex).setValue(decodeURIComponent(rowData[6].v)); // receiver_name
    sheet2.getRange('D' + rowIndex).setValue(decodeURIComponent(rowData[10].v)); // delivery_date
    sheet2.getRange('E' + rowIndex).setValue(decodeURIComponent(rowData[5].v)); // receiver_address
    sheet2.getRange('F' + rowIndex).setValue(decodeURIComponent(rowData[6].v)); // receiver_name
    sheet2.getRange('G' + rowIndex).setValue(decodeURIComponent(rowData[10].v)); // supplier_address
    sheet2.getRange('N' + rowIndex).setValue(decodeURIComponent(rowData[5].v)); // currency
  }

  // Create a download link for the new Google Sheet
  var newSpreadsheetId = newSpreadsheet.getId();
  var downloadLink = "https://docs.google.com/spreadsheets/d/" + newSpreadsheetId + "/export?format=xlsx";
  
  Logger.log("Download link: " + downloadLink);

  return ContentService.createTextOutput(downloadLink);
}

排查关键点 #

1. URL参数拼接错误：原代码中用&file_name={file_name}拼接参数，若Apps Script URL无初始参数，需改为?file_name={file_name}，否则参数无法被正确识别。
2. 部署权限问题：检查Apps Script部署设置，若选择“仅限组织内用户”，需将Cloud Function的服务账号添加为授权用户；若需匿名访问，部署时需选择“任何人，甚至匿名”。
3. 文件名编码问题：文件名含特殊字符（空格、中文等）时需URL编码，修改Cloud Function代码：

   from urllib.parse import quote
   apps_script_url_with_params = f'{apps_script_url}?file_name={quote(file_name)}'
   

4. BigQuery数据延迟：Cloud Function插入数据后立即调用Apps Script可能存在同步延迟，需确保errors = client.insert_rows_json(...)为空（数据写入成功）后再触发，或添加短暂延迟。
5. 日志查看位置：Apps Script的Logger.log需在脚本编辑器的“查看>日志”中查看，或改用console.log并关联Google Cloud项目查看云日志，避免遗漏日志。

内容的提问来源于stack exchange，提问作者diabyanas