Javascript - Convert languages excel to google sheets

Corevalence
October 11, 2023
245 views
0 votes
2 Answers

Apologies if this thread have already been listed or if I am doing something wrong for what ever reason, I don’t know forums that well.

I have a piece of code that I am struggling with. I started using google sheets recently and as oppose to Microsoft excel sheets which uses VBA, google sheets uses Java coding which I am not familiar with.

All I want to do, is to move a row from one sheet to another once it has been marked as done in the 4th cell. So either move it or copy it to second sheet and delete it from original sheet.

Here is my online document I have – https://docs.google.com/spreadsheets/d/1kzzSH5WaJFdU4I76CAjDKpBSfGK3iglZ3mGid00oldQ/edit?usp=sharing

Here is the code I used in Excel;

Sub button()
    For Each myCell In Selection.Columns(4).Cells
   If myCell.Value = "Done" Then
    myCell.EntireRow.Copy Worksheets("Done").Range("A" & Rows.Count).End(3)(2)
   myCell.EntireRow.Delete
    End If
   Next
End Sub

Thank you in advance.

I tried finding a VBA to Java language converter, but did not succeed. I tried using the google sheet language converter, but it told me that I need to pay or subscribe or something, (am living in a third world Country, I prefer food over luxury).

I tried googling code, but to no avail and finally I tried writing my own code, but did not succeed.
I struggle to find time to learn the Java coding as I work 2 jobs a day to make a living.

Again, I am very sorry for asking help here.

Here is the code that I wrote and tried that did not work.

function Button(d) {
      var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
      var DoneSheet = activeSpreadsheet.getSheetByName('Done');
      var activeSheet = SpreadsheetApp.getActiveSheet();
      var numColumns = activeSheet.getLastColumn();
      var cell = d.range;
      var value = activeSheet.getLastColumn.value;
      var lock = LockService.getScriptLock();
 
           if (activeSheet.getSheetName() == 'Dev Jobs' && activeSheet.getLastColumn.value == 'Done') {
 
        lock.tryLock(10000);

        if (!lock.hasLock()) {
 
          activeSpreadsheet.toast('The Spreadsheet is busy currently, please try again in a bit.');
 
        } else {
     
      var row = activeSheet.getRange(cell.getRow(), 1, 1, numColumns).getValues();
 
      DoneSheet.appendRow(row[0]);
 
      activeSheet.deleteRow(cell.getRow());
 
        }
    }
}

I tried this;

if (activeSheet.getSheetName() == 'Dev Jobs' && activeSheet.getLastColumn.value == 'Done') {
 
    lock.tryLock(10000);
 
   if (!lock.hasLock()) {
 
      activeSpreadsheet.toast('The Spreadsheet is busy currently, please try again in a bit.');
 
   } else {
 
 var row = activeSheet.getRange(cell.getRow(), 1, 1, numColumns).getValues();
 
  DoneSheet.appendRow(row[0]);
 
  activeSheet.deleteRow(cell.getRow());
 
    }
  }
}

Answers

Here is an example of moving specific rows to another sheet. This process will only work with values as formula references may break down.

function myTest() {
  try {
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let sheet1 = spread.getSheetByName("Sheet1");
    let sheet2 = spread.getSheetByName("Sheet2");
    let values = sheet1.getDataRange().getValues();
    // if you need to remove header then values.shift();
    let results = [];
    let rows = [];
    values.forEach( (row,index) => {
        if( row[3] === "Done" ) {  // Column D
          results.push(row);
          rows.unshift(index+1);  // which row needs to be deleted
        }
      }
    );
    sheet2.getRange(sheet2.getLastRow()+1,1,results.length,results[0].length).setValues(results);
    console.log(results);
    // now delete the rows from sheet1 starting with last
    rows.forEach( index => {
        sheet1.deleteRow(index);
      }
    );
  }
  catch(err) {
    console.log(err);
  }
}

Reference

const SHEET_NAMES = {
  dev_jobs: "Dev Jobs",
  done: "Done"
};
function rows_move_status_done() {
  const SHEET_FROM_NAME = SHEET_NAMES.dev_jobs;
  const SHEET_TO_NAME = SHEET_NAMES.done;
  const EXPECTED_COLUMN_LABEL = "Status";
  const EXPECTED_COLUMN_VALUE = "Done";

  const sheet_from = SpreadsheetApp.getActiveSpreadsheet().getSheets().filter((sheet) => sheet.getName() == SHEET_FROM_NAME)[0];
  const sheet_to = SpreadsheetApp.getActiveSpreadsheet().getSheets().filter((sheet) => sheet.getName() == SHEET_TO_NAME)[0];

  const sheet_from_protection = sheet_from.protect().setWarningOnly(true);
  // if trying to edit, people will receive a warning asking for confirmation
  // it is to give enough time to avoid manual edits which could
  // change the data after rows is attained on next line, so moved data might is correct

  const rows = sheet_from.getDataRange().getValues();
  const status_index = rows[0].findIndex(value => value == EXPECTED_COLUMN_LABEL);
  //rows[0] is row number 1
  const remove_expected = (columns, row_number) => {
    if (columns[status_index] == EXPECTED_COLUMN_VALUE) {
      sheet_to.appendRow(columns);
      sheet_from.deleteRow(row_number);
      return true;
    }
    return false;
  }

  rows.forEach((value, index) => remove_expected(value, index + 1));
  //index + 1 to reflect excel expected row_number
  sheet_from_protection.remove();
}

function onEdit(e) {
  if (e.range.getSheet().getName() === SHEET_NAMES.dev_jobs) {
    rows_move_status_done();
  }
}

Please signup or login to give your own answer.

Click here to cancel reply.

Javascript – Convert languages excel to google sheets

Answers