Google Apps脚本导出副本模板电子表格的空白副本 [英] Google Apps Script Exporting Blank Copy of Copied Template Spreadsheet

问题描述

我有一个包含内容的源电子表格.

I have a source spreadsheet with content.

我有该内容的目标模板.

I have a destination template for that content.

我选择并复制源电子表格中的内容.

I select and copy the content in the source spreadsheet.

我创建目标模板的副本并将其粘贴到源电子表格内容中.

I create a copy of the destination template and paste in the source spreadsheet content.

然后我执行一些代码以将目标模板导出为XLSX文件并将其附加到电子邮件中.

I then execute some code to export the destination template as an XLSX file and attach it to an email.

电子邮件和附件通过，但是XLSX文件的内容与原始模板匹配-我粘贴的内容丢失了.

The email and the attachment come through, but the contents of the XLSX file match the original template - the content I pasted in is missing.

但是，如果我使用导出字符串并通过浏览器运行它，它将导出带有内容的XLSX文件！

Yet if I take my export string and run it through the browser, it exports the XLSX file just fine with the contents!

似乎在内容粘贴完成之前正在运行导出功能，并发送不带内容的新创建的目标模板.

It appears the export function is running before the content paste is complete and sending the newly created destination template without the contents.

我已经尝试过Utilities.sleep(30000)，但是无论等待多长时间，我总会得到原始模板的空白副本. WTF?！

I've already tried Utilities.sleep(30000), but no matter how long I wait, I always get a blank copy of the original template. WTF?!

完整代码:

function sendVendor() {
  // Open the Active Spreadsheet
  var ssMaster = SpreadsheetApp.getActiveSpreadsheet();

  var sheetInsert = ssMaster.getSheetByName('Insert RFQ');
  var rfqNumber = sheetInsert.getRange('AW2').getValue();

  var sheetWorkUp = ssMaster.getSheetByName('WORK UP');

  var backgroundColor = '#FF5050';
  var row = 11;
  var newSheetRow = 13;
  var numRows = 0;

  var valuesPartNumbers = sheetWorkUp.getRange(12, 2, 233, 1).getValues();

  var size = valuesPartNumbers.filter(function(value) { return value != '' }).length;

  size = size - 1;

  // Create the new RFQ from Template
  var template = DocsList.getFileById('1M2f5yoaYppx8TYO_MhctEhKM_5eW-QCxlmJdjWg9VUs');  // Quote Workup Template
  var newRFQ = template.makeCopy('Vendor RFQ Request ' + rfqNumber);
  var newRFQId = newRFQ.getId();

  var folderNew = DocsList.getFolder('Vendor RFQ Requests');
  newRFQ.addToFolder(folderNew)

  // Open new RFQ
  var ssTemplate = SpreadsheetApp.openById(newRFQId);
  var sheetVendorRequest = ssTemplate.getSheetByName('Vendor Request');

  var newTemplateURL = ssTemplate.getUrl();

  var needPricing = new Array();
  var valuesFRCosts = sheetWorkUp.getRange(12, 8, size, 1).getValues();

  for (var i = 0; i < valuesFRCosts.length; i++) {
    row++;

    if (valuesFRCosts[i][0] == '') {      
      var sheetWorkUpRow = sheetWorkUp.getRange(row, 1, 1, sheetWorkUp.getLastColumn());
      sheetWorkUpRow.setBackground(backgroundColor);

      var sendToTemplate = sheetWorkUp.getRange(row, 1, 1, 6).getValues();
      sendToTemplate[0].splice(2, 1);

      sheetVendorRequest.getRange(newSheetRow, 2, 1, 5).setValues(sendToTemplate);

      newSheetRow++;      
    }
  }

  var url = 'https://docs.google.com/feeds/download/spreadsheets/Export?key=' + newRFQId + '&exportFormat=xlsx';
  var doc = UrlFetchApp.fetch(url);

  var attachments = [{fileName:'Vendor RFQ Request ' + rfqNumber, content: doc.getContent(),mimeType:"application/vnd.ms-excel"}];
  MailApp.sendEmail("[e-mail]", "Excel Export Test", "See the attached Excel file.", {attachments: attachments}); 
}

推荐答案

解决方案是在导出之前(var url之前)添加SpreadsheetApp.flush();.复制后，SpreadsheetApp仍被模板占用(在内存中)-刷新模板，然后导出，强制脚本对该新Spreadsheet进行新调用并获取最新数据.

The solution is to add SpreadsheetApp.flush(); prior to the export (just before var url). The SpreadsheetApp was still occupied with the template (in memory) after being copied - flushing it, and then exporting, forces the script to make a new call to the new Spreadsheet and get the latest data.

谢谢你，Faustino Rodriguez.

Thank you, Faustino Rodriguez.

这篇关于Google Apps脚本导出副本模板电子表格的空白副本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！