0

I would like to copy lastRows from 'Source1' sheet to 'Destination1' sheet. Triggers are on every change form 'Source1'.

The code below can help me to do the job.

function copyLastRow() {  
var target = SpreadsheetApp.openById('xxxxx-keyfile').getSheetByName('Destination1'); // copy data into this file & sheet.
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Source1'); // name of source sheet. 
var lastrow = sheet.getLastRow();
var sourceData = sheet.getRange(lastrow, 1,1, 37).getValues();
target.appendRow(sourceData[0]);}

I also set up another function as below. This function is for automatically clearing data from 'source 1' on specify time (e.g: everyday at 1 am).

function clearRange() {
var sheet = SpreadsheetApp.getActive().getSheetByName('Source1');
sheet.getRange('A2:F').clearContent();}

When function clearRange() run at 1 am on everyday, data on 'source1' with range A2:F will be clear. At that moment, the range 'A1:F1' will be as the LastRow and it will be copied to 'Destination1' as the copyLastRow() function is still running.

What I would do is the copyLastRow() function don't copy the range A1:F1 as the LastRow.

I'm quite new in not only GAS but also JS...so I would appreciate if anyone could understand and help me to solve my problem.

The mentioned code above was copied from this post.

0

Just check to see if the first item in the sourceData Array is not the same value that is in A1 before you copy the data over. I am assuming this is a title row and that you don't have anyone named "name" in your data set.

var sourceData = sheet.getRange(lastrow, 1,1, 37).getValues();
if (sourceData[0][0] != sheet.getRange(1, 1).getValue();){
  target.appendRow(sourceData[0]);
  }
}
  • You save not only my day but also help me to improve my understanding on GAS. Thank you in advance. :) – Võ Tự Đức Feb 5 '17 at 15:46
  • Ah, I'm not quite clear on If statement. There are two [0][0] in the if condition? I don't know what does it mean? Could you please explain to me? if (sourceData[0][0] != sheet.getRange(1, 1).getValue();){ (sourceData[0][0] != sheet.getRange(1, 1).getValue();){ target.appendRow(sourceData[0]); } } – Võ Tự Đức Feb 5 '17 at 15:55
  • The array is 2-D, which means there are arrays in the array [[data,data][data,data][data,data]] getting sourceData[0][0] is getting the first element of the first array. You will work with 2-D arrays a lot with google sheets. – Hink Feb 5 '17 at 17:28
  • Great to know that @Hink. :) – Võ Tự Đức Feb 5 '17 at 17:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.