How do I copy data from multiple Google sheets and paste into master Google sheet while maintaining format?

Question

Each day I paste assignments to employees in each employees respective sheet. These assignments have hyperlinks in portions of the data. At the end of each day I copy their assignments for that day and paste at the bottom of a master sheet to have record of what each employee has done daily.

I have attempted at a few different scripts to automate, but each one only performs parts of this task. The first one worked perfectly, but only copied values. Once I resolved that, my updated script would no longer paste at the bottom but instead paste over the top of data already in the master sheet.

My goal is to copy data from all sheets while maintaining their proper format, and pasting them at the bottom of a master sheet with some rows in between so you can distinguish each employees assignment each day.

Any advice would be greatly appreciated.

The script below gets me very close, but it only pulls one sheet. When I have tried to make adjustment to include a second sheet I have had no luck. I have copied the function and just inputted the next sheets, but then it only ran the last function. I need the script below to do what it is currently doing, but I need it to pull data from "110', '310', '410', and so on.

function mynewFunction() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('110'),true);
  spreadsheet.getRange('A1:L').activate();
  var target = SpreadsheetApp.openById("1Er2RyznPqkGERBjgCVVyEWelRPKYhPBri7BjO1yv6wQ");
  var source_sheet = spreadsheet.getSheetByName('110');
  var target_sheet = target.getSheetByName("target");
  var source_range = source_sheet.getActiveRange();
  var last_row = target_sheet.getLastRow();
  var values=source_range.getValues();
  source_sheet.getRange(1, 1, values.length, values[0].length).copyTo(target_sheet.getRange(last_row + 2,1));
  spreadsheet.getRange('A2').activate();

Answer 1

Just some questions:

1. You say you copy data from each of their sheets? Why not use IMPORTRANGE to pull their Live data to a sheet?
2. If you looking for Formats from their sheets then rather look at using Conditional Formatting Rules so any required formats will update dynamically rather that using manual formats.

Another approach will be to have a Master Data Sheets that imports all the required sheets into one sheets and you can do this by using an array (see sample below) and then either query the data or use a Pivot Table. And if you really want to have a cool dash board Use Google Data Studio to look at your Master Sheet.

Having a Master Data Sheet from multiple sheets using an array:

=SORT({IMPORTRANGE("Sheet URL or ID","Data");IMPORTRANGE("Sheet URL or ID","Data");IMPORTRANGE("Sheet URL or ID","Data")})

Notes:

1. The Sort function is to remove blanks spaces between Sheets.
2. You may need to validate you have access to each sheet first - So import one at a time to first validate you have access
3. Using Named Ranges from the Source Sheets helps to avoid having to type the Sheet Name!A1:Z:100
4. { } is the start and end of the Array
5. ; is to say that each sheet data is below the previous set
6. All Sheets need the same number of columns or the array will fail.

I hope this helps you re-look at using a script - rather just have live data.