Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.7k views
in Technique[技术] by (71.8m points)

Google Sheets: Create a Dropdown menu maintaining font formatting within the source range

I have a Google Sheet with a list of formatted text that is generated based on user input.

enter image description here

I now want to create a dropdown menu based on this list with the color formats still intact as it is added information for the user.

Is this possible? If not, what other workaround is there?

Will greatly appreciate any assistance.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

The problem is that, there is no way to format the dropdown choices. Aside from that, this should copy the style of the inRange cells to the outRange cells.

var sheets = SpreadsheetApp.getActiveSheet();
var inRange = sheets.getRange('A2:A4');
var outRange = sheets.getRange('C2:C10');

function createDropDown(){
  var rangeRule = SpreadsheetApp.newDataValidation().requireValueInRange(inRange).build();
  outRange.setDataValidation(rangeRule); // set dropdown to your range
}

function onEdit() {
  var inValues = inRange.getValues().flat();
  var outValues = outRange.getValues().flat();
  var inStyles = inRange.getRichTextValues().flat();
  var outStyles = [];

  outValues.forEach(function(outValue){
    var match = inValues.indexOf(outValue);
    outStyles.push([inStyles[match]]);
  });

  outRange.setRichTextValues(outStyles);
}

Sample:

sample output


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...