Macro: Copying values from one sheet to another, depending on sheet name, and specific columns

Question

First time posting here, pretty new to coding Google Scripts, so I appreciate whatever help I can get.

I'm trying to design a macro and have gone through some threads but haven't found something that fits. I've tried to design the process as program-friendly as I can imagine, hopefully it'll help. The setup is as follows:

Single Spreadsheet, multiple Sheets. A MainSheet, and an ever increasing amount of secondary Sheets (in the example, there's SheetA and SheetB) Example Sheet Structure
Column Structure for MainSheet is: SheetName, Date, Column1, Column2. Column Structure for the secondary sheets is always the same: Date, Column 1, Column2. MainSheet Column Structure Secondary Sheet Column Structure

The macro is meant to copy data from Column1 and Column2 on MainSheet, and based on the information provided on the SheetName and Date columns, paste it on the respective cells on the secondary sheets. Repeat this process for each entry on MainSheet.

I here an image example of the process: Example MainSheet Example SheetA Example SheetB

Again, I'd appreciate whatever help I can get. Thank you very much!

Edit: Here is a Link to the example Google Sheets spreadsheet.

--- Valraz

Hey Valraz, welcome to Stackoverflow! I would like to know a few things of the date column: how does it behave? Can you have multiple rows with the same date for the same Sheet? Also, I imagine that SheetA, SheetB... 's records should be sorted by this field, too? Finally, and just for the record, please remember next time to attach example Sheets documents rather than images. ;) — carlesgg97
Hey Carl, thanks for reaching out! To answer your questions: 1) The Date column in the secondary sheets is a timeline, where with each passing month of the year, a new entry will be added for each month. There will only be one record per month. 2) Yes, secondary sheet records will be sorted by Date as time goes by. 3) Sure, I'll see how to attach an example document right away :D — Valraz Krants

Cooper Cooper · Accepted Answer · 2019-10-30T17:21:42

This function will look for columns in the Sheets A,B... that match columns in the master and append that data for Sheet Names in ColumnA. It doesn't care how the columns in the sheets are laid out. So it runs a little slower than assuming a fix layout. But I was getting bored doing it the same way all of the time.

function copyData() {
  var ss=SpreadsheetApp.getActive();
  var msh=ss.getSheetByName('MainSheet');
  var mrg=msh.getRange(1,1,msh.getLastRow(),msh.getLastColumn());
  var vA=mrg.getValues();
  var hA=vA[0];
  var hObj={};
  hA.forEach(function(e,i){if(e){hObj[e]=i;}});
  vA.forEach(function(row,i){
    if(i>0) {
      var pA=[];
      var sh=ss.getSheetByName(row[hObj['SheetName']]);
      var shA=sh.getRange(1,1,1,sh.getLastColumn()).getValues()[0];
      var shObj={};
      shA.forEach(function(e,i){if(e){shObj[e]=i;}});
      row.forEach(function(c,j){
        if(shObj.hasOwnProperty(hA[j])) {
          pA.push(c);
        }
      });
      sh.appendRow(pA);
    }
  });
}

This is the straight forward way to do it.

function copyData1() {
  var ss=SpreadsheetApp.getActive();
  var msh=ss.getSheetByName('MainSheet');
  var mrg=msh.getDataRange();
  var vA=mrg.getValues();
  vA.forEach(function(r,i){
    if(i>0) {
      var sh=ss.getSheetByName(r[0]);
      sh.appendRow([r[1],r[2],r[3]],r[4]);//Modified for more columns
    }
  });
}

Macro: Copying values from one sheet to another, depending on sheet name, and specific columns

4 Answers