How to write the MIN, MID, MAX enums in Google She

Question

How can I use an InterpolationPointType enum in the following script?

See the commented lines with the hand pointers to see where I'm hitting an error: Invalid value at 'requests[0].add_conditional_format_rule.rule.gradient_rule.midpoint.type' (TYPE_ENUM)

I'm excited about what I'm designing here and think I'm close to achieving a script that can dynamically add color gradient conditional formatting to a Google Sheet.

But I haven't found a way around this enum problem even when looking at docs for ConditionalFormatRule, Gradient rules, and Add a conditional color gradient across a row.

function addGradients(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var sheet = ss.getSheetByName("temp");
  var requests = [];

  //Do this for as many ranges as needed:
  var myRange = Sheets.newGridRange();
  myRange.sheetId = sheet.getSheetId();
  myRange.startRowIndex = 12;
  myRange.endRowIndex = 24;
  myRange.startColumnIndex = 4;
  myRange.endColumnIndex = 4;  
  var request1 = addGradientToRange(myRange);
  requests.push(request1);

  // Batch send the requests  
  var batchUpdate = Sheets.newBatchUpdateSpreadsheetRequest();
  batchUpdate.requests = requests;
  var response = Sheets.Spreadsheets.batchUpdate(batchUpdate, ss.getId());
}

function addGradientToRange(myRange){
  var rule1GradientRule = Sheets.newGradientRule();
  var ptMax = Sheets.newInterpolationPoint();
  var ptMid = Sheets.newInterpolationPoint();
  var ptMin = Sheets.newInterpolationPoint();  
  var colorGreen = Sheets.newColor();
  colorGreen.green = 1;
  var colorRed = Sheets.newColor();
  colorRed.red = 1;
  var colorWhite = Sheets.newColor();  

  ptMax.color = colorGreen;  
  ptMid.color = colorWhite;
  ptMin.color = colorRed;
  ptMax.type = "MAX";//                

            

标签： google-apps-script google-sheets google-spreadsheet-api

举报

Answer 1

How about this modification?

Modification points :

• There is no "MID" in the InterpolationPointType. If you want to give a middle value, you can use "PERCENT" for type and "50" for value.
• If you want to give "white" to the middle, please use 1, 1 and 1 for red, green and blue, respectively. Ref

The modified script is as follows. I modified addGradientToRange().

Modified script :

function addGradientToRange(myRange){
  var rule1GradientRule = Sheets.newGradientRule();
  var ptMax = Sheets.newInterpolationPoint();
  var ptMid = Sheets.newInterpolationPoint();
  var ptMin = Sheets.newInterpolationPoint();  
  var colorGreen = Sheets.newColor();
  colorGreen.green = 1;
  var colorRed = Sheets.newColor();
  colorRed.red = 1;
  var colorWhite = Sheets.newColor();
  colorWhite.red = 1; // Added
  colorWhite.green = 1; // Added
  colorWhite.blue = 1; // Added

  ptMax.color = colorGreen;  
  ptMid.color = colorWhite;
  ptMin.color = colorRed;
  ptMax.type = "MAX";
  ptMid.type = "PERCENT"; // Modified
  ptMid.value = "50"; // Added
  ptMin.type = "MIN";
  rule1GradientRule.maxpoint = ptMax;
  rule1GradientRule.midpoint = ptMid;
  rule1GradientRule.minpoint = ptMin;

  var rule1 = Sheets.newConditionalFormatRule();
  rule1.ranges = [myRange];
  rule1.gradientRule = rule1GradientRule;

  var request1 = Sheets.newRequest();
  var addConditionalFormatRuleRequest1 = Sheets.newAddConditionalFormatRuleRequest();
  addConditionalFormatRuleRequest1.rule = rule1;
  addConditionalFormatRuleRequest1.index = 0;
  request1.addConditionalFormatRule = addConditionalFormatRuleRequest1;
  return request1;
}

Sample result :

This sample was applied to the range of "A1:E20".

If I misunderstand your question, I'm sorry.