Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Get entire contents of cell – not just values

I am writing a script in Google sheets to pre-populate a cell with a simple drop box when a new row is added. The top row of the spreadsheet has a simple drop box selector with a couple of choices in it – in cell A1. The drop box is not connected to anything and is not used for any actions. I just want to copy that drop box into the first cell of each new row that I add to the sheet. So far, the script I have will get the selected value of the drop box in A1 and insert that value into the first cell of the new row – but I want the entire drop box with all the choices to be added to each new row – not just the selected value. Here’s my script so far

function myFunction(e) {
 var sh = SpreadsheetApp.getActiveSheet();
 if(e.changeType === 'INSERT_ROW') {
 var row = sh.getActiveRange().getRow();
 var range = sh.getRange(1,1); 
 var dropbox = range.getValue();
 sh.getRange(row, 1).setValue(dropbox);
  }
}

So the GetValue() is not what I need. Is there a way to get the entire drop box from A1 and copy it to the first cell in each new row added? It would also be fine to programmatically create a simple drop box in the first cell of each new row, but I haven’t found a way to do that either. Any help is greatly appreciated.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>Solution :

I think you mean data validation as a drop box? If so, substitute in something like this code that gets the DataValidation rather than the value:

 var row = sh.getActiveRange().getRow();
 var range = sh.getRange(1,1); 
 var dropbox = range.getDataValidation();
 sh.getRange(row, 1).setDataValidation(dropbox);

To illustrate this process on a sheet I made shown here, running the below function myFunction() will set the data validation (drop boxy) for cells a2:a10 to be the same as cell a1.

/** @OnlyCurrentDoc*/

function myFunction() {
  const ss = SpreadsheetApp.getActiveSheet();
  var pullValidation = ss.getRange("A1").getDataValidation();
  ss.getRange("A2:A10").setDataValidation(pullValidation);
}
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading