基于AppScript实现Web表单上传文件按字段值重命名

针对你的需求，我们需要修改两个核心部分：层级文件夹的创建逻辑和自定义文件名的生成规则，同时调整参数传递以获取必要的字段值。以下是修改后的完整代码及关键部分解释：

function doGet(e) {
  return HtmlService.createHtmlOutputFromFile('forms.html').setTitle("CCC Receipt Uploader");
}

function submitReceipt(
  data,
  file,
  vendor,
  contact_info,
  account,
  amount,
  date_transaction,
  description_of_expense,
  notes
)
{
  /*Main function to run after submitting receipt.
  
  This function 
   (1) Uploads the submitted receipt to google drive.
   (1) Adds entry to relevant finance sheet containing submitted metadata and reference to uploaded receipt.
  */
  console.log("account")
  console.log(account)

  try {

    // Get time of receipt submission.
    var date_submission = new Date();
    
    // Get new receipt id number.
    var transaction_id = get_transaction_id();
   
    // Upload receipt and get location. 新增传递生成文件名所需的参数
    var receipt_location = upload_receipt(transaction_id, data, file, vendor, date_transaction, account, amount)

    // amount should be negative
    amount = -1 * amount
    
    // Add metadata and receipt to finance sheet.
    add_item_to_expense_sheet(
      transaction_id,
      date_submission,
      date_transaction,
      vendor,
      contact_info,
      amount,
      account,
      description_of_expense,
      notes,
      receipt_location
    );
 
    return ["OK", transaction_id];
    
  } catch (f) {
    return f.toString();
  }
  
}


function upload_receipt(
  transaction_id,
  data,
  file,
  vendor,
  date_transaction,
  account,
  amount
)
{
  /*Uploads receipt to google drive with hierarchical folder structure and custom filename.*/
  
  // 加载根收据文件夹
  var rootFolder = DriveApp.getFolderById('obscuredid');
  
  // 处理交易日期，转换为年/月格式（YYYY/MM）
  var transactionDate = new Date(date_transaction);
  var year = transactionDate.getFullYear().toString();
  var month = (transactionDate.getMonth() + 1).toString().padStart(2, '0'); // 月份从0开始，需+1并补零
  
  // 检查年文件夹是否存在，不存在则创建
  var yearFolder = rootFolder.getFoldersByName(year).next() || rootFolder.createFolder(year);
  // 检查月文件夹是否存在，不存在则创建
  var monthFolder = yearFolder.getFoldersByName(month).next() || yearFolder.createFolder(month);
  
  // 提取原始文件扩展名
  var fileExtension = file.split('.').pop();
  
  // 按规则生成新文件名：ReferenceID_Name_Date_Category_Amount.扩展名
  // 格式化交易日期为YYYY-MM-DD格式，避免文件名含斜杠
  var formattedDate = `${year}-${month}-${transactionDate.getDate().toString().padStart(2, '0')}`;
  // 处理金额，保留原始数值（注意submitReceipt中会转为负数，这里使用传入的原始amount）
  var formattedAmount = Math.abs(amount).toString(); // 若需要保留负数可直接用amount.toString()
  var newFileName = `${transaction_id}_${vendor}_${formattedDate}_${account}_${formattedAmount}.${fileExtension}`;
  
  // 解码文件数据并创建Blob
  var contentType = data.substring(5, data.indexOf(';')),
      bytes = Utilities.base64Decode(data.substr(data.indexOf('base64,') + 7)),
      blob = Utilities.newBlob(bytes, contentType, newFileName),
      uploadedFile = monthFolder.createFile(blob);

  // 返回上传文件的ID（原逻辑返回文件夹ID，此处改为文件ID更实用，若需保持原逻辑可调整）
  return uploadedFile.getId();
}


function add_item_to_expense_sheet(
  transaction_id,
  date_submission,
  date_transaction,
  external_account,
  contact_info,
  amount,
  internal_account,
  description_of_expense,
  notes,
  receipt_location
) {  
  /*Adds new entry to expense sheet.*/
  
  // Stupid way of filling out entire row of sheet.
  var account_category = "UNKNOWN";
  var status = "incomplete";
  var notes = "vendor contact: " + contact_info + "\n" + notes
  
  // New row to be input.
  var new_data = [ 
    transaction_id, 
    date_submission, 
    date_transaction, 
    status,
    internal_account, 
    account_category,
    amount,
    external_account,
    description_of_expense,
    receipt_location,
    notes
    ];
  
  // Open budget sheet.
  var ss = SpreadsheetApp.openById('obscuredid');
 
  // Get relevant sheet.
  var sheet = ss.getSheetByName("house_invoices");
  
  // Set data.
  sheet.appendRow(new_data)
}


function get_transaction_id() {
  /*Ggenerates next transaction id.
  
  NOTE: Currently this function reads from a document containing the current transaction
  number and then increments by 1. This is obviously very fragile.
  */
  
  var doc = DocumentApp.openById('obscuredid');
  
  var body = doc.getBody();
  
  var paragraphs = body.getParagraphs();
  
  var current_reference_number = paragraphs[1].getText();
  
  var newnumber = parseInt(current_reference_number,10)+1
  
  var newnumber_str = pad(newnumber,6);
  
  var text = paragraphs[1].editAsText();
  
  text.deleteText(0,5);
  
  text.appendText(newnumber_str)
  
  return newnumber_str
  
}


function pad(num, size) {
    var s = "000000000" + num;
    return s.substr(s.length-size);
}

function getScriptURL() {
  return ScriptApp.getService().getUrl();
}

关键修改说明 #

• 参数传递调整：在submitReceipt调用upload_receipt时，新增传递vendor、date_transaction、account、amount字段，用于生成自定义文件名；同步更新upload_receipt的参数列表。
• 层级文件夹创建：从date_transaction提取年份和月份，生成YYYY/MM层级结构；先检查年文件夹是否存在，不存在则创建，再检查月文件夹，确保文件按时间分类存储。
• 自定义文件名生成：提取原始文件扩展名，按规则拼接文件名，其中日期格式化为YYYY-MM-DD（避免非法字符），金额处理为绝对值（需保留负数可直接使用amount.toString()）。
• 返回值调整：原逻辑返回transaction_id文件夹的ID，修改为返回上传文件的ID，更便于后续在表格中直接关联具体文件（需保持原逻辑可改为return monthFolder.getId()）。

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