Today’s post will shed some light on how you can use one of the very handy basic analytics tools – Google Sheets – even more effectively.
Often, analysts need to work a lot with tables and create selections for various conditions. In working with tables, the problem usually lies in the fact that you have to process and organize large amounts of data stored on different sheets. For example, you keep track of customers on one sheet of a spreadsheet, uploading data from third-party services, and you would like to add or change the data on another sheet. Let’s see how easy and simple we suggest to solve this problem.

Script editor

If you have a Google account and data sheets uploaded to Google Sheets, then you can create a script for this table. Select the table in which you want to automate the transfer of information from one sheet to another, open it and select the “Script Editor” item from the “Tools” menu. The browser will redirect you to the Apps Script page, where you can create and edit scripts for the table.

Automating line transfer to another sheet

So, our script should automatically perform the task of line transfer to another sheet. To do this, we create one more column in the table, in which it will be possible to check the box to transfer the line or remove it to cancel this action. Let’s see how exactly this is done.
When writing a script, you can use a trigger function that fires when a certain condition is met. You can read more about trigger functions in the documentation. In our script, we use the OnEdit(e) function, which is triggered when a table cell is edited (toggle the checkbox on and off). We will call this function at the end of the script, but for now, we will write everything that should happen in the function.

function myScript(e) {   
  // We set the following condition for the function: you only need to react to clicking the checkmark in the eighth column on the "Лиды-воронка" sheet.
  if (e.source.getActiveSheet().getName() == "Лиды- воронка" && e.range.getColumn() == 8)
  {
    // Saving the objects of the source sheet and the destination sheet
    destSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test');
    sourceSheet = e.source.getActiveSheet();
    // We clear the destination sheet, starting from the second line, since we have a title in the table.
    destSheet.getRange(2, 1, destSheet.getLastRow(), destSheet.getLastColumn()).clearContent();
    // We go through all the cells with check marks, looking for those cells in which the check marks are put down.
    range = sourceSheet.getRange(1, 1, sourceSheet.getLastRow(), sourceSheet.getLastColumn());       
    for (i = 2; i <= range.getNumRows(); i++) 
    {      
      // We get all the checkboxes.
      if (range.getCell(i,8).getValue())
      {        
        // If the checkbox is filled, the current line is transferred to a new sheet.
        currentRow = sourceSheet.getRange(i, 1, i, sourceSheet.getLastColumn());           
        destSheet.appendRow(currentRow.getValues()[0]);
      }      
    }    
  }

// Then, we call a trigger function that will call our script every time we edit a cell.
function onEdit(e) {
  myScript(e)
}

As a result, if a check mark in a column is selected, then the corresponding row will be transferred to another sheet. Also, it is important to remember that when you remove a check mark, the line is removed from another sheet.
In our example, we got the following two tables:

Conclusions

Today we showed you an example of a script that will simplify, and maybe even improve, the routine work with tables. Of course, many more processes can be automated with Google Sheets scripts. You can read about other features yourself, and we’ll cover some of them in detail in future posts!