Use array of invoice numbers to create invoice objects within which each invoice number from the initial array serves as id property

user_17343215 0
Viewed 265

Building a script in google apps script.

  • I get values from an invoice data sheet with multiple lines per invoice so as to account for line items.

  • My progress so far has been to extract individual invoice numbers from the column (each invoice number occurs as many line items the individual invoice has).

  • The array todaysInvoices looks like this: [35033817, 35033818, 35033819, 35033820, 35033821]

  • Now, I need a way to create an object for each of these invoice numbers that has different properties (such as invoiceDate and customerName etc.). The initial invoice number as in the array should thereby be assigned as 'id' property to the new invoice object.

  • I need help to use objects in javascript.

  • If you require additional information, please let me know.

Below is a screenshot of a simplified version of my order sheet:

This is a clipping of my order sheet. Before and after the shown columns there are many more with more details but the hierarchies of information are already in the image

enter image description here

Below is the code I have so far:

const orderSheet = SpreadsheetApp.openById('SPREADSHEETID').getSheetByName('SHEETNAME');
const invoiceTemplate = DriveApp.getFileById('DOCUMENTID');
const tempFolder = DriveApp.getFolderById('FOLDERID');
const invoiceData = orderSheet.getRange(4,7, orderSheet.getLastRow() - 1, 57).getDisplayValues().filter(function (rows){ return rows[0] === 'INVOICED'});
const invDataRepo = SpreadsheetApp.openById('SPREADSHEETID2');
var timestamp = new Date();

function printBulkInvoices() {
  logLineItems ();
  var todaysInvoices = uniqueInvIDs ();
  todaysInvoices.sort();
  todaysInvoices.map(String);
  //fetchInvData (todaysInvoices);
  Logger.log (todaysInvoices)


}



function fetchInvData (invoiceIDs) {

let invoices = {
}
Logger.log(invoices)
  invoiceIDs.forEach
}


function fetchLineItems (invoiceDataArray) {
}

// send array of todays unique invoice numbers (later all inv data?) to invdata sheet and log them
function logTodaysInvoices (invIDArr){
invIDArr.forEach
  invDataRepo.getSheetByName('invdata').getRange(invDataRepo.getSheetByName('invdata').getLastRow()+1,1,invIDArr.length,1).setValue(invIDArr);
}

// return an array of unique invoice ids from todays invoice data
function uniqueInvIDs (){
  let singleArray = invoiceData.map(row => row[5]);
  let unique = [...new Set(singleArray)];
  return unique;
}

  //log incoicedata to invdatarepo-sheet 'lineitems'
function logLineItems (){
invDataRepo.getSheetByName('lineitems').getRange(invDataRepo.getSheetByName('lineitems').getLastRow()+1,2,invoiceData.length,invoiceData[0].length).setValues(invoiceData);
}
google-drive-api google-apps-script google-sheets javascript-objects javascript
1 Answers

It's hard to say exactly what you need since we cannot see your Invoice Data Sheet. But here's something that might give you a start:

let iobj = {idA:[]};
[35033817, 35033818, 35033819, 35033820, 35033821].forEach((id => {
  if(!iobj.hasOwnProperty(id)) {
    iobj[id]={date: invoiceDate, name: customName, items:[]};
    iobj.idA.push(id);//I find it handy to have an array of object properties to loop through when I wish to reorganize the data after it's all collected
  } else {
    iobj[id].items.push({item info properties});//I am guessing here that you may wish to addition additional information about the items which are on the current invoice
  }
});

To follow up from your question:

Your loop to collect object data would start to look something like this:

function getInvoiceData() {
  const ss = SpreadsheetApp.getActive();
  const ish = ss.getSheetByName('Invoice Data');
  const isr = 2;
  const hA = ish.getRange(1, 1, 1, ish.getLastColumn()).getValues()[0];
  let idx = {};//object return head index into row array based on header title which in this case I assume invoice number is labeled 'Invoicenumber'
  hA.forEach((h, i) => {idx[h] = i});
  const vs = ish.getRange(isr, 1, ish.getLastRow() - isr + 1, ish.getLastColumn()).getValues();
  let iobj = { idA: [] };
  vs.forEach(r => {
    if (!iobj.hasOwnProperty(r[idx['invoicenumber']])) {
      iobj[r[idx['invoicenumber']]] = { date: r[idx['invoicedate']], name: r[idx['customername']], items: [] };
      iobj.idA.push(r[idx['invoicenumber']]);
    } else {
      iobj[r[idx['invoicenumber']]].items.push({ iteminfoproperties:'' });
    }
  });

}
user_72150910
Related