CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Mike T
Mike T

Reputation: 11

Google Script Copy Last Row, All Columns from

Two tabs on a Sheet named Master and Working.

"Master" Sheet is populated by an import range from a separate google sheet which is populated by a jotform submission. =IMPORTRANGE("1YIxcke-hskw2NyokxtnWoDO_69hz_K-CXfsIq0Z3yNM", "Sheet1!A:MO")

My need is when a new submission hits "Master" the script runs to copy the new row to "Working" with select Columns.

I have two Scripts currently. The manual copy version works fine but only works on the active cell. The onEdit version does not copy anything when you run it. I have a feeling this is related to the importrange making the "last row" an empty row.

This is my script:

function onEdit(){
var ss = SpreadsheetApp.getActive();
var sheet = 
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Master");
var rowIdx = sheet.getActiveRange().getRowIndex();
var );
Logger.log(rowValues);
var destValues = [];
destValues.push(rowValues[0][0]);// copy data from col A to col A
destValues.push(rowValues[0][1]);// copy data from col B to col B
destValues.push(rowValues[0][2]);// copy data from col C to col C

destValues.push(rowValues[0][5]);// copy data from col F to col D
destValues.push(rowValues[0][6]);// copy data from col G to col E
destValues.push(rowValues[0][7]);// copy data from col H to col F
destValues.push(rowValues[0][8]);// copy data from col I to col G

destValues.push(rowValues[0][21]);// copy data from col V to col H

destValues.push(rowValues[0][23]);// copy data from col X to col I

var dest = 
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Working");

dest.getRange(dest.getLastRow()+1,1,1,destValues.length) 
.setValues([destValues]);
}

(I had to break up the dest.getRange line to make it format as code.)

This is a copy of my sheet, shared: https://docs.google.com/spreadsheets/d/1FylLCocxDmKYISzjuFeSCSLUgE4kbBtkDPk-zI698JQ/edit?usp=sharing

Upvotes: 1

Views: 1058

Answers (1)

terrywb
terrywb

Reputation: 3956

I suspect that the code is attempting to write to a row that does not exist.

Replace

dest.getRange(dest.getLastRow()+1,1,1,destValues.length) 
   .setValues([destValues]);

With

dest.appendRow(destValues)

The function "appendRow" exits on the the Sheet object. https://developers.google.com/apps-script/reference/spreadsheet/sheet#appendRow(Object)

It expects an array of values.

The setValues function you have been calling expects and array of arrays.

Upvotes: 0

Related Questions