Skip to content
Advertisement

How to clip list in cell in spreadsheet with Google Apps script?

Images: image1 , image2

I made a list as you can see in the image1. You can understand the list better if you look at image2.

The list is in cell A1. I want to copy this list to cell B1 with google app script. getValue(), getValues() don’t work. These commands take the data selected in the list. But I want to get all the data on the list. Is there a command that I can get all the data in the list?

function myFunction() {
  var app = SpreadsheetApp; 
  var spreadSheet = app.getActiveSpreadsheet();
  var sheetName = spreadSheet.getSheetByName("Sheet1");


  var listValue = sheetName.getRange("A1").getValue();
  sheetName.getRange("B1").setValue(listValue);

}

This code prints only the selected value in the list into cell B1.

Advertisement

Answer

You only need to copy the data validation and set it to the destination cell. Also don’t forget to copy the value if you need that to be the same as well.

Code:

function myFunction() {
  var app = SpreadsheetApp; 
  var spreadSheet = app.getActiveSpreadsheet();
  var sheetName = spreadSheet.getSheetByName("Sheet1");

  var dataValidation = sheetName.getRange("A1").getDataValidation();
  var listValue = sheetName.getRange("A1").getValue();
  sheetName.getRange("B1").setDataValidation(dataValidation); // make B1 a dropdown like A1
  sheetName.getRange("B1").setValue(listValue); // set value of B1 same with value of A1
}

Sample Input:

input

Sample Output:

output

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement