Google-sheets – Automatically copy first 3 columns from one sheet to each new sheet in Google Sheets

google sheets

I'm having columns A, B and C filled with data in Sheet1. Those columns contains data that is static for each new sheet. Currently, I need to manually copy the first three columns to each new sheet I create.

How can I automate the process that each time I click on the + (Add Sheet), to have that columns copied?

UPDATE: I tried to implement a similar solution found here https://stackoverflow.com/questions/55269902/automatically-copy-data-between-google-sheets

Even though this seems like a good starting point, my lack of knowledge on this matter overcomes my efforts. I tried modifying this function a bit, but didn't get any results.

Best Answer

Here's a roadmap to implement this functionality yourself using a simple Google Apps Script Trigger.

Create a trigger onEdit(e) that iterates through all sheets, checking whether there are any empty ones (i.e. newly created). If it none are empty, job done. If it finds an empty one, it reads in columns A, B, C from your first sheet and sets the values of the empty sheet to those. Job done.

Alternatively, you can delete the empty sheet and copy the static sheet.

Yet another way of doing it is to create a new menu item (basically an add-on) that simply creates a new template with the right columns filled in when clicked.

Yes, you'll have to learn basic Apps Script for it, but it's nothing extraordinarily complicated. This should help you get started for how to read and write cells: https://stackoverflow.com/questions/11334296/google-sheet-script-set-cell-value.

Code

function result(range) {
  var splitCol = 1; // split on column B
  var output2 = [];
  for(var i=0, iLen=range.length; i<iLen; i++) {
    var s = range[i][splitCol].split("\n");    
    for(var j=0, jLen=s.length; j<jLen; j++) {
      var output1 = []; 
      for(var k=0, kLen=range[0].length; k<kLen; k++) {
        if(k == splitCol) {
          output1.push(s[j]);
        } else {
          output1.push(range[i][k]);
        }
      }
      output2.push(output1);
    }    
  }
  return output2;
}

Explanation

The script evaluates each row, and particularly the second column of each row (in JavaScript arrays are zero based, so column 2 corresponds to index 1 of the array). It splits the contents of that cell, into multiple values and uses the "\n" as delimiter (line feed). After that it adds the existing info to an array and only add the individual results, when it hits index 1 (k == 1). The newly prepared row is then added to another array, that's being returned to show the result.

Screenshot

data
enter image description here

result
enter image description here

Example

I've created an example file for you: multi-line cells into new rows.

Add the script under Tools > Script editor.
Save the script, the name doesn't matter.
Use the custom function in a cell just like a built in (see the screenshot above).

Best Answer

Related Solutions

Google-sheets – Copy data from previous sheet

Google-sheets – Google sheets split multi-line cell into new rows (+ duplicate surrounding row entries)

Code

Explanation

Screenshot

Example

Related Topic