0

In Google Sheets, I have a data range that includes 10 cells with a dropdown list of businesses that send us invoices. When any of those cells are marked, meaning we got an invoice that day, we have another range ("Sheet Log!B71:V80) that fills in additional information (date, payment, etc) along with the business name. I have a code that pastes this range to the last row on a different sheet.

  // COPIES INVOICES AND PRINTS THEM TO INVOICES SHEET
  var invoice_source = ss.getRange ("Sheet Log!B71:V80");
  var destInvoiceSheet = ss.getSheetByName("Invoices List");
  var destInvoiceRange = destInvoiceSheet.getRange(destInvoiceSheet.getLastRow()+1,1);
  invoice_source.copyTo (destInvoiceRange, {contentsOnly: true});

It works fine for the most part, but the next time the function runs and pastes it, it pastes the content to 10 rows down from the actual first empty row. It appears that, even when most of the rows in the Sheet Log range are empty, they are still considered as used rows. This may be because the cells, although showing no info, do have commands on them (IF(cell="","",DATE() as a simplified example.

Is there a way to modify this code to only paste the cells in this range that are NOT blank (in the range's case, not showing "" in the cell?)

I have tried the appendRow() method but it does not appear to work for a range of multiple rows, just the topmost row. I tried implementing a for loop to paste cells that are not equal to "" but I am not the best with Google Apps Script. Best I was able to do was creating a code line to append each row in the range individually but this is very inefficient.

General Grievance
  • 4,555
  • 31
  • 31
  • 45
Clay
  • 1
  • Do you have any array formulas on the sheet? If so that will mess up the lastRow calculation. – Cooper May 22 '23 at 18:17
  • Yes we do. In that case, I'll try and redo the sheet to have that not be an issue. – Clay May 22 '23 at 20:33
  • It will be difficult to implement but we will if we have to. The table that we type into when we receive an invoice is then referenced to another table. This table (let's call it the print table) has conditional formulas on it to check if we fill the information in, it will display the date and some other information. If we leave the row blank, it will display "", which is messing up the calculation like you said. I was curious if there's a way instead use the appendRow method, on a for loop, so it will only paste each row that is NOT blank (or displaying "")? – Clay May 24 '23 at 15:23
  • Perhaps this function might help: https://stackoverflow.com/a/72842856/7215091 – Cooper May 24 '23 at 19:41

0 Answers0