code a macro on google sheets

[ines123456]

Hello !

I have a question about the macro on google sheets:

I have created a google sheets with a "prospects" page to list my customers. Each column corresponds to a piece of information and each row corresponds to a customer.
I would like to code a macro so that for a given row, when I enter "yes" in the "make a proposal" column, the whole row is transferred to another page called "proposal made".

I hope my directions are understandable, thank you in advance for your code ideas!

 

[smozgur]

Welcome to the MrExcel Message Board!

Try the following code in Apps Script.

/** @OnlyCurrentDoc */
function onEdit(e) {
  var src = e.source.getActiveSheet();
  var rng = e.range;
  if (src.getSheetName() == 'prospects' &&
      rng.getValue().toLowerCase() == 'yes' &&
      rng.offset(1 - rng.getRow(), 0).getValue() == 'make a proposal') {
    var row = rng.getRow();
    var sht = e.source.getSheetByName('proposal made');
    var trg = sht.getRange(sht.getLastRow() + 1, 1);
    src.getRange(row + ':' + row).moveTo(trg);
    src.deleteRow(row);
  }
}

Please note that the sample code assumes:

1. Source sheet name is prospects.
2. Target sheet name is proposal made.
3. The entry column's first cell contains make a proposal as the header of the column.
4. The entire row from the source sheet is copied to the next available row in the target sheet, and the source row is deleted.

 

[DWelchUser]

Is it possible to create a macro in Google Sheets?
Open your spreadsheet in Google Sheets, then go to Tools Macros Record Macro. This will open the Recording New Macro box at the bottom of your spreadsheet. Whatever you click or type in Google Sheets now will be recorded—and will be repeated in that order when you run that macro.

------------------------------------------
Coding for kids begins with the fundamentals and progresses to the development of core coding skills.

 

[ines123456]

Welcome to the MrExcel Message Board!

Try the following code in Apps Script.

/** @OnlyCurrentDoc */
function onEdit(e) {
  var src = e.source.getActiveSheet();
  var rng = e.range;
  if (src.getSheetName() == 'prospects' &&
      rng.getValue().toLowerCase() == 'yes' &&
      rng.offset(1 - rng.getRow(), 0).getValue() == 'make a proposal') {
    var row = rng.getRow();
    var sht = e.source.getSheetByName('proposal made');
    var trg = sht.getRange(sht.getLastRow() + 1, 1);
    src.getRange(row + ':' + row).moveTo(trg);
    src.deleteRow(row);
  }
}

Please note that the sample code assumes:

1. Source sheet name is prospects.
2. Target sheet name is proposal made.
3. The entry column's first cell contains make a proposal as the header of the column.
4. The entire row from the source sheet is copied to the next available row in the target sheet, and the source row is deleted.

Thank you for your answer !

I just have a problem with the definition of my variables, I don't know how to do it

I sent you a screenshot with all the undefined variables !
Can you tell me how to do it ?

 

[smozgur]

"prospects" should be the source sheet name that you enter "yes".
"faire proposistion" should be the top cell value of the "yes" column.
"proposition realisee" should be the target sheet name that you move the rows from the source sheet.

I used the string values that you used in your code, just make sure they match with the corresponding values. For example, is "faire proposistion" correct with two s in French? Or is it supposed to be "faire proposition"?

Basically, the strings in the code should exactly match the actual sheet names and the cell values.

If still having the problem then you can share a sample sheet on Google Drive (not the real sheet with real data), so we can access and take a look.