Google-sheets – How to copy data from multiple Google sheets and paste into master Google sheet while maintaining format

copy/pastegoogle sheets

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();

Best Answer

Just some questions:

You say you copy data from each of their sheets? Why not use IMPORTRANGE to pull their Live data to a sheet?
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:

The Sort function is to remove blanks spaces between Sheets.
You may need to validate you have access to each sheet first - So import one at a time to first validate you have access
Using Named Ranges from the Source Sheets helps to avoid having to type the Sheet Name!A1:Z:100
{ } is the start and end of the Array
; is to say that each sheet data is below the previous set
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.

Related Solutions

Google-sheets – Preventing paste from overwriting cell formatting in Google Sheets

Does the person making the booking really need access to the spreadsheet? Would it be better to make a Google Form which they enter their number and the course into, and which then puts the data into another sheet which has all the helper columns to fill in the rest of the details.

Google-sheets – Automatically copy and paste cells that have just been updated to a list from forms

You need onFormSubmit, not onEdit. Open the documentation of classes Spreadsheet, Sheet, and Range, and look up each of the methods used below. To give you a general idea of what is done here:

ss = active spreadsheet, sheet = active sheet
values = an array of values in the active sheet. Note that arrays in JavaScript are 0-indexed.
row = the row added to sheet when the onFormSubmit event happened. This one is 1-based, so the adjustment row-1 is used when addressing arrays.
values[row-1][1] - the value in column B of this row. [Row number][Column Number] in 0-based indexing.

The script isn't perfect. For one thing, it assumes the target has rows available. You may want to use insertRowAfter() to be safe. Or just create a lot of rows. The logic for other things you mentioned is not here either, but it's essentially the same.

function onFormSubmit(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var values = sheet.getDataRange().getValues();
  var row = event.range.getRow();
  if (values[row-1][1] == 'Luke') {   // column B in 0-based indexing 
    var targetSheet = ss.getSheetByName('for Luke');
    var lastRow = targetSheet.getLastRow();
    var targetRange = targetSheet.getRange(lastRow+1, 1, 1, values[0].length);
    targetRange.setValues([values[row-1]]);
  }   
}

Best Answer

Related Solutions

Google-sheets – Preventing paste from overwriting cell formatting in Google Sheets

Google-sheets – Automatically copy and paste cells that have just been updated to a list from forms

Related Topic