CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Stz
Stz

Reputation: 61

How To transform OnEdit script to Run-by-button script (copy cells from 1 sheet to another with rule)

I've made a well-working script to copy cells *OnEdit (col16 on sheet 2) if any row in Col16 = "Yes") to last row of custom column on sheet 1(now Col4). But I have a Query based data on sheet 2, so my script copies data only when I type "Yes" by hands. That's why now I need to run this script by button, or maybe as a time-driven trigger. Can't get it to work by button. How to change the following script to make it possible to work by clicking on button? 

function onEdit(e) {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
 if (sheet.getName() !== '2' || e.range.getColumn() !== 16 || e.value !== 'Yes') return;
  var value = e.range.offset(0,-12,1,1).getValue();
  sheet = e.source.getSheetByName('1');
  var grd = sheet.getRange("D:D").getValues();
  var maxIndex = grd.reduce(function(maxIndex, row, index) {
  return row[0] === "" ? maxIndex : index;
  }, 0);
  sheet.getRange(maxIndex+2,e.range.getColumn()-12,1,1).setValue(value);
}

UPDATE: I've tried to change my script like this, assigned it to button, and after clicking on button- it say "Running..... then Finished..." but it don't copy anything (but it works when it's OnEdit(e), and I m not getting any error. But it seems it's not enough just change "OnEdit(e)" to "copymissings()", I need to change something in code, but I don't know what exactly. Please help:

function copymissings() {
  var s = SpreadsheetApp.getActive();
  if (s.getName() !== '2' || range.getColumn() !== 16 || value !== 'Yes') return;
  var value = range.offset(0,-12,1,1).getValues();
  sht = s.getSheetByName('1');
  var grd = sheet.getRange("D:D").getValues();
  var maxIndex = grd.reduce(function(maxIndex, row, index) {
  return row[0] === "" ? maxIndex : index;
  }, 0);
  sht.getRange(maxIndex+2,range.getColumn()-12,1,1).setValues(value);
}

Visualisation: CLICK TO SEE GIF

Upvotes: 0

Views: 735

Answers (2)

Stz
Stz

Reputation: 61

Okay big thanks to Edward Ulle from "Plus" google apps script forum.

We've made working version of same script as I offered in post which is automatically working OnEdit.

The following script copies cells from Sheet-2 Col4 if that row contains "Yes" in col16 to Sheet-1 col4. Everything optional.

function copymissings() {
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    // Source sheet
    var shs = ss.getSheetByName("2");
    //  Values from Sheet2
    var vs = shs.getRange(1,1,shs.getLastRow(),shs.getLastColumn()).getValues();
    // Destination sheet
    var shd = ss.getSheetByName("1");
    // Get the destination range
    var rd = shd.getRange("D:D");
    // Gets a 2D array
    var vd = rd.getValues();
    var j = 1;
    // Get offset j to first empty row
    for( j=0; j<vd.length; j++ ) {
      if( vd[j][0] == 0 ) break;
    }
    //  Assuming you want to do for every row in Sheet2
    for( var i=0; i<vs.length; i++ ) {
      if( vs[i][15] == "Yes" ) {  // Column 16 is index 15
        // Use offset from last row of destination sheet
        rd.offset(j,0,1,1).setValue(vs[i][3]);  // Copy to column 4 (0)
        j++; // Increment to next row
      }
    }
  }
  catch(err) {
    Logger.log(err);
  }
}

Upvotes: 0

ReyAnthonyRenacia
ReyAnthonyRenacia

Reputation: 17651

I think you're mixing two concepts here. onEdit() is a trigger that runs AUTOMATICALLY when a user changes the value of a cell. However what you're trying to do is MANUAL, as the user is in charge when the event will trigger.

Refer to HTML Service: Create and Serve HTML on how create UI buttons. You can check actual samples here.

Upvotes: 0

Related Questions