CODEWITHSUNDEEP
javascriptjavagoogle-apps-scriptgoogle-sheets
Gatra Di Yoga Tama
Gatra Di Yoga Tama

Reputation: 13

google apps script - write data in next empty row

with google apps script I am collecting data from other spreadsheet. This is set to collect data daily and writing that in to a sheet “DATABASE”.

So far I have this as a basic solution, but this is always write data and replace the existing data.

In my script I want to copy data from import range spreadsheet to specific column And what I want is continuously to copy the data to the next empty row, but how to do this without erasing existing copied data?. PLEASE HELP!.

var scriptProperties = PropertiesService.getScriptProperties();
var ssid = '1usRuvazJlxAGvF0G2-e00MEQ_AjMCOnWopBFX4qfUcc';
var sheetName = 'DATABASE';

function CopyDatabase() {
  var sheet = SpreadsheetApp.openById(ssid).getSheetByName(sheetName);
  var startRow = 3;
  var numRows = sheet.getLastRow() - 1;
  var dataRange = sheet.getRange(startRow, 1, numRows, sheet.getLastColumn());
  var data = dataRange.getValues();
  var Copy = "Copy";
  var newRow = sheet.getLastRow() + 1;
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    if (row[5] != Copy) {
      var Code = row[0];
      var orderDate = row[1];
      var custName = row[2];

      sheet.getRange(startRow + i, 5).setValue(Code);
      sheet.getRange(startRow + i, 6).setValue(orderDate);
      sheet.getRange(startRow + i, 7).setValue(custName);

    }
  }
}

Here is the link of database (Google sheet) :

https://docs.google.com/spreadsheets/d/1usRuvazJlxAGvF0G2-e00MEQ_AjMCOnWopBFX4qfUcc/edit#gid=0

Screenshoot

Upvotes: 0

Views: 3519

Answers (2)

Iamblichus
Iamblichus

Reputation: 19339

If I understand you correctly, you want to copy the rows from columns A-D (where column D is not Copy) to columns E-H, to the first empty row of these target columns.

If that's the case, you can:

  • Get the first empty row in columns E-H via getNextDataCell(direction).
  • Filter out the rows not containing Copy from the source data, using filter.
  • Use setValues(values) to copy the filtered rows to the destination columns (starting at first empty row).

Code snippet:

function CopyDatabase() {
  var sheet = SpreadsheetApp.openById(ssid).getSheetByName(sheetName);
  var startRow = 3; 
  var numRows = sheet.getLastRow() - startRow + 1; 
  var startCol = 1;
  var numCols = 4;
  var startColTarget = 5;
  var dataRange = sheet.getRange(startRow, startCol, numRows, numCols); 
  var data = dataRange.getValues(); 
  var Copy = "Copy"; 
  var firstEmptyRow = sheet.getRange("E3:H3").getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow() + 1;
  var dataToCopy = data.filter(row => row[3] !== Copy);
  sheet.getRange(firstEmptyRow, startColTarget, dataToCopy.length, dataToCopy[0].length).setValues(dataToCopy);
}

Upvotes: 2

doubleunary
doubleunary

Reputation: 19185

It may be easiest to copy the data to a different tab in the spreadsheet. Try replacing the for loop in your code with this:

  const targetSheetName = 'Archive of Daily Data';
  const targetSheet = sheet.getParent().getSheetByName(targetSheetName)
    || sheet.getParent().insertSheet(targetSheetName);
  data
    .filter(row => row[3] === Copy)
    .forEach(row => targetSheet.appendRow([row[0], row[1], row[2]]));

Upvotes: 1

Related Questions