google-apps-script - 当我在文件夹中添加新发票时，自动在报告中添加新行

Question

首先，我会对你说实话。我对脚本和 JavaScript 编码真的很陌生。我认为我足够聪明，可以理解我在做什么，仍然。

我是一名自由职业者，我使用表格中的 Google 文档套件管理我的所有发票。所以，我的驱动器中有一个文件夹，其中包含我所有的发票作为单独的电子表格，我还有另一个单独的报告电子表格，位于不同的文件夹中。报告表使用 IMPORTRANGE 函数直接从发票表中获取数据。到目前为止，我总是在报告表中手动设置所有内容。我要做的是在文件夹中添加新发票时自动在报告中添加新行。

我发现一个脚本似乎做了我想做的事情，但在我修改后它不起作用。最初，该脚本用于从 google 表格中获取电子邮件地址并自动将它们导入到一张主表格中。

我尝试根据需要对其进行修改，但出现错误。

我找到的代码来自谷歌论坛。询问是：“我希望每次将其添加到我的谷歌驱动器时，自动从谷歌工作表中提取一列电子邮件地址。这是否可能？目前工作表作为 Excel 工作表开始，但已转换通过“Multcloud”同步上传到谷歌表格。我知道我可以使用带有表格键的 importrange 命令从该特定表格导入我想要的范围，但是有没有办法捕获上传的新表格的密钥和还导入这些范围？

如果没有，任何人都可以想出一种更好的方法，以便在每次将新工作表添加到驱动器时​​将范围导入到同一个工作表中。”

代码运行后手动。目前似乎没有办法避免这种情况，但应该不会花太长时间。”

使用原始代码：

function emailAddressExtractor() {
  // Change folder ID to match yours. Upload your spreadsheets (with the email addresses) into this folder. 
  var folder = DriveApp.getFolderById("1j3GZqUgVOwywARvKyRJnj6p_gLNMCk__"); 
  var files = folder.getFiles(), file;
  var ss = SpreadsheetApp.getActiveSpreadsheet();


  // Creates sheets for "Email Addresses" and "List of URLs", unless they exist already
  if (ss.getSheetByName("Email Addresses") == null) {
    var create = ss.insertSheet("Email Addresses");
  }
  if (ss.getSheetByName("List of URLs") == null) {
    var create = ss.insertSheet("List of URLs");
  }


  // Variables to make setting active sheet easier
  var emailAddressesSheet = SpreadsheetApp.setActiveSheet(ss.getSheetByName("Email Addresses"));
  var listOfUrlsSheet = SpreadsheetApp.setActiveSheet(ss.getSheetByName("List of URLs"));


  // Finds last rows and columns of emails and URLs, so that they are not overwritten later by new emails and URLs
  var oldUrls = listOfUrlsSheet.getRange("A1:A").getValues();
  var oldUrlsLength = oldUrls.filter(String).length +1;
  var oldEmails = emailAddressesSheet.getRange("1:1").getValues();
  var oldUrlsLength = oldEmails.filter(String).length;


  // Counts number of files in folder and gets URLs 
  var urlsList = [];
  var counter = 0;
  while (files.hasNext()) {
    counter++;
    file = files.next();
    var urls = [];
    urls.push(file.getUrl())
    urlsList.push(urls);
  }


  // Finds new URLs by comparing to old ones 
  Array.prototype.diff = function(a) {
    return this.filter(function(i) {return a.indexOf(i) < 0;});
  }
  var diff = urlsList.diff(oldUrls);  // This is the difference between the old URLs and the new ones
  Logger.log("\n" + diff);

  for (var i = 0; i < diff.length; i++) { // This loop inserts the new URLs into the spreadsheet. This is done so that the next time the code is run it won't duplicate the emails
    var row = i + oldUrlsLength;
    var diffRange = listOfUrlsSheet.getRange("A"+row)
    diffRange.setValue(diff[i]);
  }


  // Inserts IMPORTRANGE formula into sheet, taking column A out of each spreadsheet in the designated folder
  for (var i = 0; i < counter; i++) {  
    var fileUrl = urlsList[i];
    var cell = emailAddressesSheet.getRange(1, oldUrlsLength+i);
    cell.setValue("=IMPORTRANGE(" + '"' + fileUrl + '"' + "," + '"' + "Sheet1!A:A" + '"' + ")" );  

  }
}

这是我现在修改的代码以仅获取发票编号：

function dataExtractor() {
  // Change folder ID to match yours. Upload your spreadsheets (with the Numbers) into this folder. 
  var folder = DriveApp.getFolderById("1jH6M0Ijsepe0MnDLULrmfHHJ8R_Fd3-M"); 
  var files = folder.getFiles(), file;
  var ss = SpreadsheetApp.getActiveSpreadsheet();


  // Creates sheets for "Data" and "List of URLs", unless they exist already
  if (ss.getSheetByName("Report") == null) {
    var create = ss.insertSheet("Report");
  }
  if (ss.getSheetByName("List of URLs") == null) {
    var create = ss.insertSheet("List of URLs");
  }


  // Variables to make setting active sheet easier
  var reportSheet = SpreadsheetApp.setActiveSheet(ss.getSheetByName("Report"));
  var listOfUrlsSheet = SpreadsheetApp.setActiveSheet(ss.getSheetByName("List of URLs"));


  // Finds last rows and columns of Data and URLs, so that they are not overwritten later by new emails and URLs
  var oldUrls = listOfUrlsSheet.getRange("A1:A").getValues();
  var oldUrlsLength = oldUrls.filter(String).length +1;
  var oldNumber = reportSheet.getRange("B7:B").getValues();
  var oldUrlsLength = oldNumber.filter(String).length;


  // Counts number of files in folder and gets URLs 
  var urlsList = [];
  var counter = 0;
  while (files.hasNext()) {
    counter++;
    file = files.next();
    var urls = [];
    urls.push(file.getUrl())
    urlsList.push(urls);
  }


  // Finds new URLs by comparing to old ones 
  Array.prototype.diff = function(a) {
    return this.filter(function(i) {return a.indexOf(i) < 0;});
  }
  var diff = urlsList.diff(oldUrls);  // This is the difference between the old URLs and the new ones
  Logger.log("\n" + diff);

  for (var i = 0; i < diff.length; i++) { // This loop inserts the new URLs into the spreadsheet. This is done so that the next time the code is run it won't duplicate the emails
    var row = i + oldUrlsLength;
    var diffRange = listOfUrlsSheet.getRange("A"+row)
    diffRange.setValue(diff[i]);
  }


  // Inserts IMPORTRANGE formula into sheet, taking column A out of each spreadsheet in the designated folder
  for (var i = 0; i < counter; i++) {  
    var fileUrl = urlsList[i];
    var cell = rapportSheet.getRange(1, oldUrlsLength+i);
    cell.setValue("=IMPORTRANGE(" + '"' + fileUrl + '"' + "," + '"' + "Sheet1!J8" + '"' + ")" );  

  }
}

我的发票编号位于每张发票的单元格 J8 中，在报告中，它们从单元格 B7 开始，一直向下。

我从谷歌脚本编辑器得到的错误是： Range not found (line 50, file "Code") 那行是： var diffRange = listOfUrlsSheet.getRange("A"+row)

这是我拥有的发票模板的链接：https ://docs.google.com/spreadsheets/d/1wE4YtvdwBBiriRcuv-k8oFyKyEuN9SSIuKewf106cdg/edit?usp=sharing

这是我使用的报告模板的链接： https ://docs.google.com/spreadsheets/d/1qScJItssyaxpsqAXVnMMrmUlBYUicAsX7CQZA7ky0Rg/edit?usp=sharing

我的发票文件夹的文件夹 ID 为：1KlGD5Qj9C5pMrOdHSwkYesK-yyq4udNC

我的报告文件夹的文件夹 ID 为：1q0Ta7h4aQHaemgwlWWMS_5YBa1WH-rSz

非常感谢各位！！！

标签： google-apps-scriptgoogle-sheets

Answer 1

您的发票、表格和电子表格的结构并不是很明显。怀着最大的敬意，在未来，您最好提供尽可能多的关于场景细节和报告目标的信息。例如，在这种情况下，不清楚发票是位于单独的电子表格（多个文件）中，还是位于单个电子表格中（一个文件）。这两种情况都很容易管理，但需要做出假设。无论如何，恕我直言，您获得的代码并不是那么有用，并且会让您偏离正题。

以下代码基于在单个“发票”电子表格中作为单独工作表存在的发票构建报告。包含报告的电子表格位于一个单独的电子表格中，但不清楚您为什么要这样做。就个人而言，我的“发票”电子表格中有报告。

发票电子表格= so_52459999_invoices
这是布局的屏幕截图。您可以查看发票数据以及每张发票的多个选项卡。还有一个从报告中排除的选项卡 (raw_data)。

---

报告电子表格= so_52459999_report
这是报告布局的屏幕截图。可以在此页面上放置一个按钮以启动宏/脚本，从而使该过程更容易。我没有“擦除”任何现有报告的内容；这是微调，您可以决定是否要这样做。该报告还收取每张发票。大概随着时间的流逝，您不太关心年初的发票，但是对日期敏感度进行编码也是一种选择。

---

function so_5245999() {

    // identify the current spreadsheet
    var ss = SpreadsheetApp.getActiveSpreadsheet()

    // get the Spreadsheet name
    var ReportSheetName = ss.getName();

    // Get the spreadhseet ID
    var ReportSheetID = ss.getId();
    //Logger.log('Report Sheet Name = '+ReportSheetName);
    //Logger.log('Report Sheet ID = '+ReportSheetID);

    // open the Invoices Spreadsheet
    // Supplied the ID
    var InvoiceSheetID = "14PfY_0oeCnr8Z1L-iiHgehAWC3HMsUiv6x1IcK-PLSE";

    var InvoiceSheet = SpreadsheetApp.openById(InvoiceSheetID);

    // get the spreadsheet name
    var InvoiceSheetName = InvoiceSheet.getName();
    //Logger.log('Invoice Sheet Name = '+InvoiceSheetName);
    //Logger.log('Invoice Sheet ID = '+InvoiceSheetID);

    // Move the cursor to cell A3 on Report sheetthis is the start of the invoice report
    var RepSheetActive = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var RepSheetRange = RepSheetActive.getRange('A3:A3');
    RepSheetActive.setActiveRange(RepSheetRange);
    //Logger.log('Report sheet = '+ReportSheetName+', and invoice sheet = '+InvoiceSheetName);

    // count the number of invoices
    InvoiceCount = invoiceCount();
    //Logger.log('Invoice count = '+InvoiceCount);  

    // set a varable for the startrow on the reports sheet
    var targetstartrow = 3;

    // Get the Invoice sheets
    var allInvSheets = InvoiceSheet.getSheets();

    // Loop through all the sheets
    for (var s in allInvSheets) {

        // Create some variables to make code and debugging  simpler
        var thisinvoice = allInvSheets[s]
        var thisInvoiceNumber = thisinvoice.getName();
        //Logger.log('Invoice sheet name = '+thisInvoiceNumber);
        //Logger.log('Record Number = '+s); 

        // How to modify startrow if the sheet isn't an invoice sheet and it is the first sheet.
        // Note: "s" is a string; must convert iot to a number.
        if (thisInvoiceNumber == "raw_data" && Number(s) == 0) {
            targetstartrow = targetstartrow + (Number(s));
            //Logger.log('AFTER1: targetstartrow = '+ targetstartrow);   
        }
        // How to modify startrow if the sheet isn't an invoice sheet and it is NOT the first sheet.
        if (thisInvoiceNumber == "raw_data" && !Number(s) == 0) {
            targetstartrow = targetstartrow + (Number(s) - 1);
            //Logger.log('AFTER2: targetstartrow = '+ targetstartrow);   
        }

        // OK, this is an ordinary invoice.
        if (thisInvoiceNumber !== "raw_data") {

            // define the source data
            source = thisinvoice.getRange(1, 2, 4, 1).getValues(); //row, column, numRows, numColumns
            //Logger.log("The values are " + source);


            // source data from a Columnar array to a Row array
            // This is important because Google Sheets don't allow "Transpose" when copying between spreadsheets.
            var rowdata = convertCol2Row(source);

            // Paste the Invoice data into the Report
            RepSheetActive.getRange(targetstartrow, 1, 1, source.length).setValues(rowdata); //row, column, numRows, numColumns

            // Increment the targetstartrow - i.e. move it down the page to the next row.
            targetstartrow = targetstartrow + 1;

        }


    } //end of  sheets loop.

}


// Utility to count the number of sheets in a spreadsheet.
// This assumes that there is one sheet that is not an invoice; that's why it returns sheets minus one.
function invoiceCount() {
    var invoiceSheet = SpreadsheetApp.openById("14PfY_0oeCnr8Z1L-iiHgehAWC3HMsUiv6x1IcK-PLSE");
    var sheets = invoiceSheet.getSheets().length;
    //if (sheets.length > 1) {
    //Logger.log("Number of sheets = "+sheets);
    //}
    return (sheets - 1);
}



// Function to convert an array from Columns to Rows
// Credit: Riel in https://stackoverflow.com/questions/32444009/google-sheets-script-copy-values-from-array-to-column
function convertCol2Row(column) {
    return [column.map(function(row) {
        return row[0];
    })];
}

---

更新 以下内容承认发票是其自己文件夹中的单独电子表格（“发票”），报告摘要是其自己文件夹中的单独电子表格。以下代码旨在附加到报告电子表格。我认为这应该足以让你继续前进。

function so_5245999_02() {

    // declare the Invoice Folder name
    var your_folder_id = getInvFolderID('Invoices');

    //Logger.log('folder id = '+your_folder_id);
    //Logger.log('folder name = '+your_folder_id.getName());

    // get the ID for the Invoices folder
    var invfldr = DriveApp.getFolderById(your_folder_id);
    //Logger.log("invfldr: " + invfldr);

    //get the files in the Invoice folder
    var files = invfldr.getFiles();

    //loop through each file
    while (files.hasNext()) {

        //get the first file
        var thisfile = files.next();

        // get its name and ID
        var thisfilename = thisfile.getName();
        var thisfileid = thisfile.getId();
        //Logger.log('Name: file = '+thisfilename);
        //Logger.log('ID: file = '+thisfileid);

        // "open" the Invoice spreadsheet
        var InvoiceSheet = SpreadsheetApp.openById(thisfileid);

        // get all the sheets in the spreadsheet
        var allInvSheets = InvoiceSheet.getSheets();

        // Loop through all the sheets (even though there may onlky be one sheet)
        for (var s in allInvSheets) {

            //Logger.log(' s: '+ s);

            // get the first (and only) sheet
            var this_invoice = allInvSheets[s];
            //Logger.log(' this_invoice: '+ this_invoice);

            //get all the data fields
            var invoicenumber = InvoiceSheet.getRange("J8").getValues();
            //Logger.log('invoicenumber: '+invoicenumber);
            var clientname = InvoiceSheet.getRange("B12").getValues();
            //Logger.log('clientname: '+clientname);
            var invoicedate = InvoiceSheet.getRange("J5").getValues();
            //Logger.log('invoicedate: '+invoicedate);
            var invoiceamount = InvoiceSheet.getRange("K32").getValues();
            //Logger.log('invoiceamount: '+invoiceamount);
            var invoicedesc = InvoiceSheet.getRange("B18").getValues();
            //Logger.log('invoicedesc: '+invoicedesc);

            //wipe the value in any previous sheet.
            var sourcedata = new Array();

            // assign the data values to an array - leave room for cells to be used later
            sourcedata = [invoicenumber, invoicedate, " ", " ", clientname, invoicedesc, invoiceamount];
            //Logger.log('source data: '+ sourcedata);

            //Convert the array to one that will paste into a row
            var rowdata = convertColumn2Row(sourcedata);

            // declare the active spreadsheet
            var ss = SpreadsheetApp.getActiveSpreadsheet();

            //get the sheet
            var sheet = ss.getSheets()[0];

            //insert a new row
            sheet.insertRowAfter(6);

            // paste the data into the new row.
            var reportdestination = sheet.getRange(7, 2, rowdata.length, rowdata[0].length).setValues(rowdata); //row, column, numRows, numColumns

        } // end for  
    } // end while
} // end function

// Function to convert an array from Columns to Rows
// Credit: Riel in https://stackoverflow.com/questions/32444009/google-sheets-script-copy-values-from-array-to-column
function convertColumn2Row(column) {
    return [column.map(function(row) {
        return row[0];
    })];
}


function getInvFolderID(folderName) {
    var folders = DriveApp.getFolders();
    while (folders.hasNext()) {
        var folder = folders.next();
        if (folder.getName() == folderName) {
            return folder.getId();
        }
    }
}

---