N
N
Nik322020-01-25 13:29:46
Google Apps Script
Nik32, 2020-01-25 13:29:46

Copy data from one sheet to another last empty row?

Please help, I'm just getting started with Google Spreadsheets. It is necessary for the script to work like this, copy the first line "list1" and transfer the last empty line to "list2". The script I copied looks like this:

function onOpen(e) {
  SpreadsheetApp.getUi()
      .createMenu('Работа с Журналом')
      .addItem('Копировать в журнал', 'myFunction')
      .addToUi();
}


function myFunction(){
  var list1 = SpreadsheetApp.getActiveSpreadsheet(); //лист откуда берем данные
  var activCell = list1.getActiveSheet().getActiveCell(); //определение активной ячейки  
  var activNum = activCell.getRow(); //номер активной ячейки
  
  var number = list1.getActiveSheet().getRange(activNum, 1).getValue(); // номер участка
  var name = list1.getActiveSheet().getRange(activNum, 2).getValue(); // имя человека
  var date1 = list1.getActiveSheet().getRange(activNum, 4).getValue(); // дата начала работ
  var date2 = list1.getActiveSheet().getRange(activNum, 6).getValue(); // дата завершения работ
  
  var list2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Журнал вода данных"); //лист куда вставляем данные


//Вот как делается на примере номера участка
  var lastEmptyRow = list2.getLastRow() + 1;
  list2.getRange(lastEmptyRow, 1).value = number;
//Дальше по аналогии для оставшихся данных
  
}

But, it throws the error "TypeError: Cannot call method 'getLastRow' of null object. (line 23, Code file)". Help.5e2c178c9d0fc358004237.jpeg
5e2c17980b1a2564103668.jpeg
5e2c17a083f5e225513137.png

Answer the question

In order to leave comments, you need to log in

1 answer(s)
A
Alexander Ivanov, 2020-01-25
@oshliaer

I would do so

/**
 *
 */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Custom menu')
    .addItem('Copy to logs', 'userActionsCopyToLogs')
    .addToUi();
}

/**
 *
 */
function userActionsCopyToLogs() {
  var headers = ['Name', 'Number'];

  var from = SpreadsheetApp.getActiveSheet();

  if (from.getName() !== 'Sheet1') {
    SpreadsheetApp.getActive().toast('Activate a range on "Sheet1"');
    return;
  }

  var fromValues = from.getDataRange().getValues();

  var fromHeaders = fromValues[0].map(function(h) {
    return headers.indexOf(h);
  });

  var activeRange = SpreadsheetApp.getActiveRange();
  var rowStart = activeRange.getRow();
  var rowEnd = activeRange.getLastRow();
  var fromData = fromValues
    .filter(function(row, i) {
      return i >= rowStart - 1 && i <= rowEnd - 1;
    })
    .map(function(row) {
      return row.filter(function(_, j) {
        return fromHeaders[j] > -1;
      });
    });

  var to =
    SpreadsheetApp.getActive().getSheetByName('Logs') ||
    SpreadsheetApp.getActive().insertSheet('Logs');

  to.getRange(to.getLastRow() + 1, 1, fromData.length, fromData[0].length)
    .setValues(fromData)
    .activate();
}

screenrecord.gif
Change the line so that data is copied from the columns you need. Full codevar headers = ['Name', 'Number'];

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question