I am trying to create a new spread sheet for each month this needs to be written to with sales from the previous day at midnight every day via a cron and the total and vat columns totaled at the end of each column.
Sales should only be added once they have been billed and an invoice number generated. And any refunded or returned orders need amending in the correct column and row.
I have all the logic to create a file each month and produce all the data I need to populate the spreadsheet, I can completely re-write the file every day but would like to avoid this if possible and simply amend the existing spreadsheet is it possible to do the following:
The data set looks like:
01/01/2019
Invoice Number | Total | VAT
1234 | £200 | £40
1235 | £100 | £20
My questions are how do I add a new set of columns for the next day 02/01/2019 and given each invoice number is unique how can I amend 1235 to 1235/pr the total to £50 and the vat to £10 on the current spreadsheet.