时间戳

首页 > 解决方案 > 此代码应该将数据从我的电子表格转换为文档，然后转换为 pdf，但它只执行最后一行

google-apps-script - 此代码应该将数据从我的电子表格转换为文档，然后转换为 pdf，但它只执行最后一行

问题描述

我有一个脚本，它应该从谷歌表格中导出数据，将其输入到文档中并将其存储在正确的文件夹中，然后获取该新文档并将其转换为 PDF，然后将其存储到它自己的文件夹中。

当我独立运行这两个函数时，它们会正确执行并继续执行，直到创建所有文档。

将它们放在一起，如下所示，它们只对最后一行数据执行此操作，而不是其他 13 行...

任何想法为什么它不循环？

function createDoc() {
  console.log("first");

  // To pull the headers of each column - insert spreadsheet URL and header array
  var headers = Sheets.Spreadsheets.Values.get('1846CxmPdoc2VBW6GxaybCPW1_u2swO1jooIBiF2Yl90', 'A2:AA2');
  // To pull the variables for each header - insert spreadsheet URL and variable array
  var variables = Sheets.Spreadsheets.Values.get('1846CxmPdoc2VBW6GxaybCPW1_u2swO1jooIBiF2Yl90', 'A3:AA14');
  // To pull the template document for the header and variables to be input by the program
  var templateId = '1ONhT3n4Pr49BL6xEM_ykO9UVi8xZriA2fVAZjoFi2qI';


  //Loop to run through all variables in spreadsheet
  for(var i = 0; i < variables.values.length; i++) {
    
    //Create the variables which will be used to transfer the data from the spreadsheet to the document. The first cell in the row is 0 and then from left to right, increases in its number. Only change variable name and the number.
    var client = variables.values[i][0];
    var agent = variables.values[i][1];
    var aaddress = variables.values[i][2];
    var acity = variables.values[i][3];
    var caddress = variables.values[i][4];
    var ccity = variables.values[i][5];
    var suopen = variables.values[i][6];
    var suclose = variables.values[i][7];
    var moopen = variables.values[i][8];
    var moclose = variables.values[i][9];
    var tuopen = variables.values[i][10];
    var tuclose = variables.values[i][11];
    var weopen = variables.values[i][12];
    var weclose = variables.values[i][13];
    var thopen = variables.values[i][14];
    var thclose = variables.values[i][15];
    var fropen = variables.values[i][16];
    var frclose = variables.values[i][17];
    var saopen = variables.values[i][18];
    var saclose = variables.values[i][19];
    var price = variables.values[i][20];
    var appayment = variables.values[i][21];
    var mpayment = variables.values[i][22];
    var junepayment = variables.values[i][23];
    var julypayment = variables.values[i][24];
    var aupayment = variables.values[i][25];
    var sepayment = variables.values[i][26];
  }

  // Make a copy of the template file
  const documentId = DriveApp.getFileById('1ONhT3n4Pr49BL6xEM_ykO9UVi8xZriA2fVAZjoFi2qI').makeCopy().getId();

  // Rename the copied file
  DriveApp.getFileById(documentId).setName('2022' + " " + client + " " + 'Pool Management Proposal');
    
  // Set copied file's destination variable;
  var folderId = DriveApp.getFolderById('1YVhLzwZ9CI5-iTR1SKHF5ykNVqZQvQY9');
  
  // Send copied file to this folder
  var fileId = DriveApp.getFileById(documentId);
  fileId.moveTo(folderId); 

  // Get the document body as a variable
  const  OpenDoc = DocumentApp.openById(documentId);
  const body = OpenDoc.getBody();

  // Insert Data from sheet to document. ##info## is the placeholder text in the document, and the second variable is the variable we set above to codify the column's data in the sheet.
  body.replaceText('##Agent Name##', agent);
  body.replaceText('##Agent Address##', aaddress);
  body.replaceText('##Agent City/Zip##', acity);
  body.replaceText('##Client Name##', client)
  body.replaceText('##Client Address##', caddress);
  body.replaceText('##Client City/Zip##', ccity);
  body.replaceText('##Contract Price##', price);
  body.replaceText('##April Payment##', appayment);
  body.replaceText('##May Payment##', mpayment);
  body.replaceText('##June Payment##', junepayment);
  body.replaceText('##July Payment##', julypayment);
  body.replaceText('##August Payment##', aupayment);
  body.replaceText('##September Payment##', sepayment);
  body.replaceText('##Sunday Open##', suopen);
  body.replaceText('##Sunday Close##', suclose);
  body.replaceText('##Monday Open##', moopen);
  body.replaceText('##Monday Close##', moclose);
  body.replaceText('##Tuesday Open##', tuopen);
  body.replaceText('##Tuesday Close##', tuclose);
  body.replaceText('##Wednesday Open##', weopen);
  body.replaceText('##Wednesday Close##', weclose);
  body.replaceText('##Thursday Open##', thopen);
  body.replaceText('##Thursday Close##', thclose);
  body.replaceText('##Friday Open##', fropen);
  body.replaceText('##Friday Close##', frclose);
  body.replaceText('##Saturday Open##', saopen);
  body.replaceText('##Saturday Close##', saclose);

  OpenDoc.saveAndClose();

  var pdfFolder = DriveApp.getFolderById("1_idXGdZo0l_U1IxuaLDUqrk0HjdfZvsg"); 
  var templateFile = DriveApp.getFileById(documentId);
  var theBlob = templateFile.getBlob().getAs('application/pdf');
  var newPDFFile = folderId.createFile(theBlob);
  newPDFFile.moveTo(pdfFolder);

  var fileName = templateFile.getName().replace(".", ""); 
  // otherwise filename will be shortened after full stop   
  newPDFFile.setName(fileName + ".pdf").getId();
}

标签： google-apps-script

解决方案

function myfunk() {
  const vs = Sheets.Spreadsheets.Values.get("ssid",'Sheet0!A1:J21');
  SpreadsheetApp.getActive().getSheetByName('Sheet1').getRange(1,1,vs.values.length,vs.values[0].length).setValues(vs.values);
}

我想过你在说什么，你是对的，它似乎不像和数组一样，所以我尝试了这个：

function myfunk() {
  const ss =  SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1');
  let vs = Array.from(Sheets.Spreadsheets.Values.get("ssid",'Sheet0!A1:J21').values);
  let va = vs.filter((r,i) => i % 2 == 0);
  SpreadsheetApp.getActive().getSheetByName('Sheet1').getRange(1,1,va.length,va[0].length).setValues(va);
}

我的工作表0：

COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8 COL9 COL10
3 14 5 10 17 1 15 10 2 6
12 10 2 19 2 1 19 6 10 7
4 7 17 11 17 3 8 7 2 5
10 17 14 13 2 6 18 6 1 11
15 3 1 1 1 19 9 1 4 14
1 6 8 8 7 10 5 5 17 12
15 1 8 4 5 7 13 19 0 7
14 18 9 10 13 3 9 9 19 14
11 1 5 17 17 15 2 6 8 16
0 14 6 4 18 12 8 8 17 4
10 15 13 7 3 17 13 17 9 3
3 16 2 2 4 7 6 3 17 3
17 18 12 13 13 10 9 14 16 11
8 6 2 10 14 7 6 0 17 12
13 12 19 9 12 11 19 14 0 18
18 17 0 0 18 9 19 14 2 1
7 9 17 12 19 0 3 17 17 16
15 11 0 6 4 18 8 16 8 15
10 4 5 10 13 8 7 3 13 9
10 2 9 11 10 0 17 2 19 7

我的工作表1：

COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8 COL9 COL10
12 10 2 19 2 1 19 6 10 7
10 17 14 13 2 6 18 6 1 11
1 6 8 8 7 10 5 5 17 12
14 18 9 10 13 3 9 9 19 14
0 14 6 4 18 12 8 8 17 4
3 16 2 2 4 7 6 3 17 3
8 6 2 10 14 7 6 0 17 12
18 17 0 0 18 9 19 14 2 1
15 11 0 6 4 18 8 16 8 15
10 2 9 11 10 0 17 2 19 7

显然，这现在表现为一个数组。

然后我意识到它必须返回 JSON，因为这也有效。

function myfunk() {
  const ss =  SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1');
  let vs = JSON.parse(Sheets.Spreadsheets.Values.get("ssid",'Sheet0!A1:J21')).values;
  let va = vs.filter((r,i) => i % 2 == 0);
  SpreadsheetApp.getActive().getSheetByName('Sheet1').getRange(1,1,va.length,va[0].length).setValues(va);
}

推荐阅读