Google Script - Multi cells copy from a sheet to another with "if empty" check

Question

First of all I would to thanks to community of Stack Overflow. The many posts here helped to build an billing data file in Google spreadsheet with Scripts to convert invoices to pdf, send them by email, clear specific cells of the invoice after pdf generation, etc.

However, I am struggling now to export sales data from the Invoice to 2 other sheets (Sales and Products) to be able to make some analysis

Invoice looks like this: Invoice It can contain 1 to 10 lines of different products

Here is the link to my spreadsheet:

https://docs.google.com/spreadsheets/d/11MDwYbCDHJy0rUtnYWEk4k8Ow0KvppNSu13B7uZWU1g/edit?usp=sharing

What I want to do:

for each non-empty line of sheet "INVOICE", I want to copy specific cells to sheet "SALES" that contains following info: Invoice #, Date, Name, Surname, Email, Ref, Product name, Quantity, Total price after rebate, Mode of payment

I want 1 line / product sold Hence, if 2 products references are sold, I will have to copy "Invoice #, Date, Name, Surname, Email and Mode of payment" twice

I tried using several functions (lastrow,etc.) but this is not a basic copy (for me at least) as I am not copying an entire row

To make it simple, I ended up writing a script that copy the 10 lines of the invoice (no matter they are empty or not) But this gives me 2 problems: my script takes ~20 sec to run and it copies Invoice #, Date, Name,... for lines that have no sales data Then I have to delete manually these lines. If I create a script to delete these lines, that would be even slowlier

In Sales sheet, I have Vlookup formulas (yellow columns) that I would like to be copied down when I create a new row or add data in a new row Again, I couldn't do that
In sheet "Products", adjust the inventory quantity each time I sell a product For this purpose, I have created a column "Sold qtties" (Column E) that I would like to increment by the qtties sold in the invoice

I would really appreciate if some of you could help me here.

ClemT ClemT · Accepted Answer · 2016-12-10T15:37:54

Your code was very slow because you process single data instead of multiples. Also for your formula issues, I change them using ARRAYFORMULA

Here is your modified code:

        function myFunction() {
      var sourceSpreadsheet = SpreadsheetApp.getActive();
      var sheetName = "INVOICE";
      var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);


      // Copy each  sales lines to 'SALES' sheet
      var destinSheet = "SALES";
      var cibleSheet = sourceSpreadsheet.getSheetByName(destinSheet);
      var cibleSheetRange = sourceSpreadsheet.getSheetByName(destinSheet);

      //DEBUT MODIFS
      var sourceSheetData = sourceSheet.getRange("A13:H22").getValues();
      var numInvoice = sourceSheet.getRange("F2").getValue();
      var dateInvoice = sourceSheet.getRange("F3").getValue();
      var typePaiement = sourceSheet.getRange("C26").getValue();
      var surName = sourceSheet.getRange("F7").getValue();
      var lastName = sourceSheet.getRange("F8").getValue();
      var email = sourceSheet.getRange("F9").getValue();
      var invoiceLine1 = [];
      var invoiceLine2 = [];
      var invoiceLine3 = [];
      var lines =0;
      var lastRow = cibleSheet.getLastRow();

  for(var i=0;i<sourceSheetData.length;i++){
     if(sourceSheetData[i][0]!=""){
        lines= lines+1;
        cibleSheet.getRange(lastRow+lines,1 ,1,2).setValues([[numInvoice,dateInvoice]]);
        cibleSheet.getRange(lastRow+lines,5 ,1,5).setValues([[surName,lastName,email,sourceSheetData[i][1],sourceSheetData[i][2]]]);
        cibleSheet.getRange(lastRow+lines,11 ,1,3).setValues([[sourceSheetData[i][0],sourceSheetData[i][7],typePaiement]]);

        //INCREMENT STOCK
        //YOU ALSO COULD USE FORMULA  =sumifs(SALES!K:K;SALES!I:I;C2;SALES!J:J;B2)
        for (var j=0;j<stockSheetData.length;j++){
           if (sourceSheetData[i][1]==stockSheetData[j][0]){
           var oldSales= stockSheet.getRange(j+1,5).getValue();
           stockSheet.getRange(j+1,5).setValue(oldSales+sourceSheetData[i][0]);
           }
       }

     }
  }

}

You can test directly in your spreadsheet if all works as expected

Google Script - Multi cells copy from a sheet to another with "if empty" check

1 Answers