Create A New Spreadsheet and Copy Over Specific Cells Whenever a New Row Is Inserted

Question

I have:

A google sheet which is automatically receiving new shopify orders through zapier
The orders are inserted as new rows
The below code, which is a container bound simple trigger, that is accessed by going to Tools > Script Editor in the google sheet

I want:

To create a new spreadsheet whenever a row is inserted into the source spreadsheet THIS PART IS WORKING
To copy over specific information from the source spreadsheet to the new spreadsheet THIS PART IS NOT WORKING

I was initially using getActiveSpreadsheet to get the sourceSpreadsheet variable, and thought maybe the active spreadsheet was changing after I created the new spreadsheet. To try to fix this I used openByID in place of getActiveSpreadsheet, but the function is still not working. Any help would be much appreciated!

// Function will run automatically on a change to the spreadsheet by looking at the event object, or e
function onChange(e){ 

// Look in the log to see what type of change is represented by the event
 Logger.log(e.changeType);

// Only run the rest of the function if the type of change represented by the event object is an inserted row 
 if(e.changeType=='INSERT_ROW'){

// Get the current spreadsheet that is the source 
 var sourceSpreadsheet = SpreadsheetApp.openById('File ID');

// Get the first sheet of the current spreadsheet (0 indexed)   
 var sourceSheet = sourceSpreadsheet.getSheets()[0];

// Get the last row of the sheet that has data in it
 var lastrow = sourceSheet.getLastRow();

// Get the value on the last row, for 3 different columns
 var sourceRange1 = sourceSheet.getRange(lastrow, 1).getValues();

 var sourceRange2 = sourceSheet.getRange(lastrow, 2).getValues();  

 var sourceRange3 = sourceSheet.getRange(lastrow, 3).getValues();

// Create new destination spreadsheet
 var newSpreadsheet = SpreadsheetApp.create("My New File Name"); 

// Get the first sheet of the new spreadsheet (0 indexed)   
 var newSheet = newSpreadsheet.getSheets()[0];

// Set destination range to put data to
 var newRange = newSpreadsheet.getRange(1,1).setValues(sourceRange1)

 var newRange2 = newSpreadsheet.getRange(1,2).setValues(sourceRange2)

 var newRange3 = newSpreadsheet.getRange(1,3).setValues(sourceRange3)

}

}

James D James D · Accepted Answer · 2020-04-02T09:36:27

Changing

var newRange = newSpreadsheet.getRange(1,1).setValues(sourceRange1);

to

var newRange = newSheet.getRange(1,1).setValues(sourceRange1);

should solve your problem.

The code could also be optimized to:

function onChange(e){ 

  Logger.log(e.changeType);

  if(e.changeType=='INSERT_ROW'){
    var sourceSpreadsheet = SpreadsheetApp.openById('File ID');
    var sourceSheet = sourceSpreadsheet.getSheets()[0];

    var newSpreadsheet = SpreadsheetApp.create("My New File Name"); 
    var newSheet = newSpreadsheet.getSheets()[0];

    var lastrow = sourceSheet.getLastRow();

    var sourceRange = sourceSheet.getRange(lastrow, 1, 1, 3).getValues();

    newSheet.getRange(1,1,1,3).setValues(sourceRange);

    }

}

Reference:

getRange(row, column, numRows, numColumns)

Create A New Spreadsheet and Copy Over Specific Cells Whenever a New Row Is Inserted

1 Answers