I am a running a new business and want to make creating an invoice easier. What I would like to do is as follows:

1. Create a new copy of my invoice template from this sheet (Invoice)
2. Fill cells B17 - B32 with the data from the order sheet from the relevant row (Order sheet)

Ideally it would use getLastRow to import the data so it only copies the latest order on the page and needs to transpose also.

My main issue is that I can't work out how to get a script to open an existing spreadsheet and copy the sheet within it.

CodePudding user response：

You can get started with this sample script that copies a specific tab from a spreadsheet to a destination sheet that you can set.

function myFunction() {
  var destSS = SpreadsheetApp.getActiveSpreadsheet(); //Get current spreadsheet
  var sourceSS = SpreadsheetApp.openById("ID"); //Spreadsheet to copy from 
  sourceSS.getSheetByName("name").copyTo(destSS).setName("Copy of "   sourceSS.getName()); //Sheet tab to copy
  
}

CodePudding user response：

Sorry if this is long.

Instead of pulling from another sheet, I would copy INVOICE to the existing OTW SHEET. I would then Protect the sheet ranges, then hide the tab.

When you are ready to build a new invoice receipt you duplicate the hidden sheet. It will let you edit the protected cells. (Before you hide it you can build a macro and a button to duplicate the hidden tab [more on this later])

I would then also build a form sheet from the info on OTW SHEET.
https://www.youtube.com/watch?v=v2X-fArILPA&t=3s&ab_channel=HayKel
(This is a great video) You will have the option to search/pull, update and delete entries in OTW SHEET. This WILL pull the last row like you wanted when you save and submit. Later on when you search it will help generate the info.

I would take it a step more and then in the blank search bar cell I would data validate a list of ranges OTW Sheet range being "INVOICE".

After all that, If you wanted to keep the same format and look of your original Invoice sheet (if it didn't build your form to look like your invoice), I would set each cell to equal the information you generated into desired cells from the copied Invoice tab.

Finally, when you run the search it should auto-generate the cells. Your Macro button will duplicate the hidden sheet with the right info. [You could even make a macro script to pull the "Invoice number" and used it to rename the new Invoice tab]. After you print your receipt just the delete the tab, the info is still saved in OTW SHEET.

You might have to play around with the scripts, but it's all there.

Hope that makes sense and that it helps. Good Luck.