Google Sheets – Copying Last Row to Another Spreadsheet on Form Submission

google sheetsgoogle-apps-script

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.

Best Answer

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]);
  }
}

Related Solutions

Google Sheets – Using setBackgroundRGB() Over setBackgroundColors()

As you (maybe) know, the fact that a method is "deprecated" does not mean it doesn't work - just that it might be removed in the future.

So you're probably not getting an error, but a warning, and your code should work as intended.

That being said, there is a replacement: It seems that setBackgroundColors has just been renamed to setBackgrounds, which it is know called in the documentation.

I tried these two functions:

function doesGiveWarning() {
  var range = SpreadsheetApp.getActiveSheet().getRange("A2:C2");
  range.setBackgroundColors([["red", "white", "blue"]]);
}

function doesNotGiveWarning() {
  var range = SpreadsheetApp.getActiveSheet().getRange("A2:C2");
  range.setBackgrounds([["red", "white", "blue"]]);
}

Running doesGiveWarning causes the script editor's lightbulb to turn on:
... and, clicking on it displays the warning,

while doesNotGiveWarning runs without any warning. In both cases, the cells are filled with the colors I specify.

However, you seem the be right, there is no plural version of setBackgroundRGB (yet). If you are willing to convert your RGB values to hex, you may still use setBackgrounds, as it accepts both CSS color names (red, white, blue) and hex values (#FF0000, #FFFFFF, #0000FF).

Google Sheets – Duplicate Template Sheet Dynamically with Protected Cells

Figured it out

function createTeamSheets() {
  var ss        = SpreadsheetApp.getActiveSpreadsheet();

// Get the range of cells that store team data.
  var teamDataRange = ss.getRangeByName("SheetName");
  var teamObjects = teamDataRange.getValues();

  var template = ss.getSheetByName('TemplateTeamSheet');

  for (var i=0; i < teamObjects.length; i++) {

    // Put the sheet you want to create in a variable
    var duplicate = ss.getSheetByName(teamObjects[i]);

    // Check if the sheet you want to create already exists. If so,
    // log this and loop back. If not, create the new sheet.


        if (duplicate) {
           Logger.log("Sheet " + teamObjects[i] + "already exists");
        } else {

          if (teamObjects[i] == '') {
           break;
        } else {
           template.copyTo(ss).setName(teamObjects[i]).showSheet();


var duplicatesheet = ss.getSheetByName(teamObjects[i]);


var p = template.getProtections(SpreadsheetApp.ProtectionType.SHEET)[0];          
var p2 = duplicatesheet.protect();

  p2.setDescription(p.getDescription());
  p2.setWarningOnly(p.isWarningOnly());  
  if (!p.isWarningOnly()) {
    p2.removeEditors(p2.getEditors());
    p2.addEditors(p.getEditors());
    p2.setDomainEdit(p.canDomainEdit()); //  only if using an Apps domain 
  }
  var ranges = p.getUnprotectedRanges();
  var newRanges = [];
  for (var i = 0; i < ranges.length; i++) {
    newRanges.push(duplicatesheet.getRange(ranges[i].getA1Notation()));
  } 
  p2.setUnprotectedRanges(newRanges);


        }
        }          
}  
return;
}

Best Answer

Related Solutions

Google Sheets – Using setBackgroundRGB() Over setBackgroundColors()

Google Sheets – Duplicate Template Sheet Dynamically with Protected Cells

Related Topic