是否有一些方法可以将特定列的信息记录在最后一行中? [英] Is there some approaches that will log information in the last row for specific columns?

问题描述

在Google工作表中，我试图将我在其他工作表中输入的一些信息记录到我的invoiceLogSheet中.但问题是:当信息记录到工作表时，它总是记录整个工作表的最后一行，而我想要的是:登录特定行的特定范围的最后一行(进一步说明:在特定的列中检查其空闲的最后一行并在该行上记录信息)

In google sheets, I tried to log some information I entered in other sheet to my invoiceLogSheet. but the problem is : when the information log to the sheet, it always log in the last row for entire sheet, and what I want is : to log in last row for specific range of columns (clarify more: to check in specific columns their free last row and log the information on this row)

但是我不知道该如何编写脚本..这是我使用的功能，它不能完成我想要的工作:

but I don't know how to script this .. this is the function I used and it does not do the job I want:

function InvoiceLog(invoice_number, part, due_date, totalInvoice, payable_to, project_name, note)
{
  
   //DEFINE ALL ACTIVE SHEETS
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  //DEFINE INVOICE LOG SHEET          
  var invoiceLogSheet = ss.getSheetByName("information"); 
  
  
  //GET LAST ROW OF INVOICE LOG SHEET
  var nextRowInvoice = invoiceLogSheet.getLastRow() + 1;
  
 
  
  //POPULATE INVOICE LOG
  invoiceLogSheet.getRange(nextRowInvoice, 2).setValue(invoice_number);
  invoiceLogSheet.getRange(nextRowInvoice, 3).setValue(part);  
  invoiceLogSheet.getRange(nextRowInvoice, 4).setValue(due_date);
  invoiceLogSheet.getRange(nextRowInvoice, 5).setValue(totalInvoice);
  invoiceLogSheet.getRange(nextRowInvoice, 6).setValue(payable_to);
  invoiceLogSheet.getRange(nextRowInvoice, 7).setValue(project_name);
  invoiceLogSheet.getRange(nextRowInvoice, 8).setValue(note);


}

我要检查的最后一行是(B:H).

the columns I want to check the last row in them is (B:H).

推荐答案

说明:

解决此问题的一种方法是使用这个问题.

基本上，对于每个选定的列，您都可以像这样计算总行数:

Essentially for each of the selected columns you calculate the total number of rows like that:

invoiceLogSheet.getRange('B:B').getValues().filter(String).length +1

请注意，这种方法将为您提供特定列中非空单元格的总数.如果您有空白单元格，那么它将为您提供比正确单元格小的数字.

Please note that this approach will give you the total number of non empty cells in a particular column. If you have blank cells, then it will give you a smaller number than the correct one.

function InvoiceLog(invoice_number, part, due_date, totalInvoice, payable_to, project_name, note)
{
  
   //DEFINE ALL ACTIVE SHEETS
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  //DEFINE INVOICE LOG SHEET          
  var invoiceLogSheet = ss.getSheetByName("information"); 
  
  
  //GET LAST ROW OF INVOICE LOG SHEET
  var nextRowInvoice = invoiceLogSheet.getLastRow() + 1;
  
  
  
  
  // new code
  //POPULATE INVOICE LOG
  invoiceLogSheet.getRange(invoiceLogSheet.getRange('B:B').getValues().filter(String).length +1, 2).setValue(invoice_number);
  invoiceLogSheet.getRange(invoiceLogSheet.getRange('C:C').getValues().filter(String).length +1, 3).setValue(part);  
  invoiceLogSheet.getRange(invoiceLogSheet.getRange('D:D').getValues().filter(String).length +1, 4).setValue(due_date);
  invoiceLogSheet.getRange(invoiceLogSheet.getRange('E:E').getValues().filter(String).length +1, 5).setValue(totalInvoice);
  invoiceLogSheet.getRange(invoiceLogSheet.getRange('F:F').getValues().filter(String).length +1, 6).setValue(payable_to);
  invoiceLogSheet.getRange(invoiceLogSheet.getRange('G:G').getValues().filter(String).length +1, 7).setValue(project_name);
  invoiceLogSheet.getRange(invoiceLogSheet.getRange('H:H').getValues().filter(String).length +1, 8).setValue(note);


}

这篇关于是否有一些方法可以将特定列的信息记录在最后一行中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！