P
P
pteslenko72019-12-04 23:43:06
Google Apps Script
pteslenko7, 2019-12-04 23:43:06

How to copy data from one sheet to another in google spreadsheet?

I will describe the situation in detail. I have a google spreadsheet. It has 2 sheets. The data in the first sheet changes regularly. There is a list of sites (this is immutable data), then a person can be attached to the site, and there is a start date and a completion date (this is mutable data). The site can be worked out by different people on different dates. Therefore, there is a need to keep a kind of log book, when and by whom a certain area was taken. The second sheet should serve as a journal entry.
It is necessary to make sure that when a person is added for a certain area + the start and completion date of work is added, then this data (together with the number of the area) is transferred to the second sheet. And inserted into the last empty cell. And so on ad infinitum. Just a journal entry.
With this script, I got access to sheets 1 and 2, received data from the active cells of sheet 1. But I can’t figure out how to put them in sheet 2.
Do not judge strictly, I am writing the script for the first time

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("Журнал вода данных"); //лист куда вставляем данные
}

Answer the question

In order to leave comments, you need to log in

3 answer(s)
S
Shohruh Shaimardonov, 2019-12-04
@pteslenko7

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;
//Дальше по аналогии для оставшихся данных
  
}

S
Sergey Sokolov, 2019-12-05
@sergiks

You can use the Range.copyTo() method

function myFunction() {
  var sourceSheet = SpreadsheetApp.getActiveSpreadsheet(); //лист откуда берем данные
  var activeRow = list1.getActiveSheet().getActiveCell().getRow(); //номер активной ячейки

  var destSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Журнал ввода данных");
  var lastRow = destSheet.getLastRow();
  destSheet.insertRowAfter(lastRow);
  var destRow = lastRow + 1;
  
  function copyCell(colFrom, colTo) {
    sourceSheet.getRange(activeRow, colFrom).copyTo(destSheet.getRange(destRow, colTo));
  }
                                                                                                      
  copyCell(1, 1); // номер участка
  copyCell(2, 2); // имя человека
  copyCell(4, 3); // дата начала работ
  copyCell(6, 4); // дата завершения работ
}

Add a call to this function in your own add. menu , you can do this:
function onOpen(e) {
  SpreadsheetApp.getUi()
      .createMenu('Работа с Журналом')
      .addItem('Копировать в журнал', 'myFunction')
      .addToUi();
}

P
pteslenko7, 2019-12-05
@pteslenko7

Is it possible to make this script run only when all fields for transfer are filled?
That is, if both the number and the name, the start date and the end date are filled in line 2, then the script transfers the data from this line to sheet 2. The same is
true for other sections. If line 3 contains the number, name, start date, and end date, then the script transfers the data from this line to sheet 2.
If any of the cells is not filled, then the data is not transferred.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question