CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Cristian Garcia
Cristian Garcia

Reputation: 3

Transferring and Deleting Data in Google Spreadsheets

I am trying to use Google Apps Script to create a function that will search for word Yes in a specific column in my Google Spreadsheet.

If it finds Yes, it will then continue to copy those entire rows onto a different spreadsheet saved to my google drive and then delete it from the original spreadsheet.

I have little to none experience with this language, but I have made some code.

I am looking for a correct way to execute this and how to complete my code. What would you do?

Newest code edit:

function moveDelete() {
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var dsh=SpreadsheetApp.getActive().getSheetByName('Sheet2');

  var data = sh.getRange(1, 1, sh.getLastRow()-1).getValues();
  var range = sh.getRange(1, 1, data.length);


  var rowsDeleted = 0;
  for (var i = 0; i <= data.length - 1; i++) {3
    var array = data[i]
    if (array[4] == 'Yes') {
      dsh.appendRow(array);
      sh.deleteRow((parseInt(i)+1) - rowsDeleted);
      rowsDeleted++;
    }
  }
};

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Send to Record",
    functionName : "moveDelete"
  }];
  sheet.addMenu("Custom Menu", entries);
};

Upvotes: 0

Views: 150

Answers (1)

Marios
Marios

Reputation: 27390

Solution 1 - transfer to the same document:

function moveDelete() {
  
  var ss = SpreadsheetApp.getActive()
  var sh = ss.getSheetByName('Sheet1');
  var dsh= ss.getSheetByName('Sheet2');  
  var data = sh.getDataRange().getValues();

  var rowsDeleted = 0;
  for (var i = 0; i <= data.length - 1; i++) {
    var array = data[i]
    if (data[i][4] == 'Yes') {
      dsh.appendRow(array);
      sh.deleteRow((parseInt(i)+1) - rowsDeleted);
      rowsDeleted++;
    }
  }
};

Solution 2 (based on comment) - transfer to another spreadsheet file:

function moveDelete() {
  
  var ss = SpreadsheetApp.getActive();
  var ts = SpreadsheetApp.openById("id of the target spreadsheet here"); // example: 31d6k7iXh20rjLn22m0axvscR6-BcyOy_fxhdk-ResXU
  var sh = ss.getSheetByName('Sheet1'); // Sheet1 of the source spreadsheet
  var dsh= ts.getSheetByName('Sheet2'); // Sheet2 of the target spreadsheet
  var data = sh.getDataRange().getValues();

  var rowsDeleted = 0;
  for (var i = 0; i <= data.length - 1; i++) {
    var array = data[i]
    if (data[i][4] == 'Yes') {
      dsh.appendRow(array);
      sh.deleteRow((parseInt(i)+1) - rowsDeleted);
      rowsDeleted++;
    }
  }
};

Note that 1. uses 4 as an index which checks for the 5th column which is column E. Make sure to get the index number right to pick the desired column.

Upvotes: 1

Related Questions