0

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

Cooper
  • 59,616
  • 6
  • 23
  • 54
Gatra Di Yoga Tama
  • 13
  • 3
  • I don't see where you have specified the second spreadsheet. – Cooper Apr 26 '21 at 13:37
  • The whole proses is on one spreadsheet, just in left and right side in worksheet – Gatra Di Yoga Tama Apr 26 '21 at 13:59
  • Hi! I posted an answer explaining how to do this. I'm not sure whether you want to copy the rows containing `Copy`, or the ones _not_ containing it. Also I'm not sure whether column `D` should be copied or not. In any case, I hope the general idea is clear. – Iamblichus Apr 26 '21 at 14:03
  • Hi!, row that contains COPY only as a filter to prevent duplication of data, according to the code, if (row [5]! = Copy) then run the script. The dynamic data is column A, B and C, and the data base that I want to write down continuously is columns E, F and G without replacing the previous data (Append row) – Gatra Di Yoga Tama Apr 26 '21 at 14:22
  • @Iamblichus Thanks you very much... – Gatra Di Yoga Tama Apr 26 '21 at 14:42

2 Answers2

1

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]]));
doubleunary
  • 13,842
  • 3
  • 18
  • 51
  • Hi! I have tried this method and it works, but what I need is to copy in one worksheet. This code gave me inspiration for different projects. Thanks! – Gatra Di Yoga Tama Apr 26 '21 at 14:29
1

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);
}
Iamblichus
  • 18,540
  • 2
  • 11
  • 27