Google Sheets Script: setValue of a Cell in a Range

153 Views Asked by At

I'm embarrassed to ask this, but despite Googling and reading the docs, I still don't understand how to update one cell in a range. The relevant parts of my function are:

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Analysis");
  var range = ss.getRange("Analysis!AF10:AM15"); // 6 rows
  var data = range.getValues();

        ... (do stuff)

        // set newAngle  <<<<<<<<<<<<DOESN'T WORK
        data[i][1].setValue = newAngle;

[edit] I've found and used the way to set the value when the range is a single cell with setValue, but I haven't found the right way to update a single cell in a range with multiple cols and rows.

Apologies for even asking for help with this!

It seems I've asked an obvious question, yet I still have no hint as to how to go about it. Here is the full function, as far as I've managed to get:

function c_Optimise_Stabilator() {
// Author: Max Hugen
// Date: 20102-12-07
// Purpose: Attempt to optimise Stab Angle to balance with Stab Target Force
// WARNING: This function may eventually cause a circular reference, so ensure there is an "escape".
//          May occur if other optimisation functions are also run?

  const ui = SpreadsheetApp.getUi();
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  var target_sheet = "Analysis";
  var target_range = "Stab_Angles";
  var sheet = ss.getSheetByName("Analysis");
  var msg = ""; // gather input for Logger
  var s = "";   // short info for testing alerts, then added to msg

  // for testing use only a few rows, rather that the entire Named Range
  var range = ss.getRange("Analysis!AF10:AM15"); // 6 rows
  //var range = c_GetRangeObjByName(target_range);
  var data = range.getValues();

  // for readability, giving range cols a name
  const Vb=0, Angle=1, Target=6, Delta=7;
  // angle range
  const maxAngle = 2.0, minAngle = -0.2, incAngle = 0.1;
  // counters
  var i=0;
  
  var originalAngle=0.0, newAngle=0.0, originalDelta=0.0, newDelta=0.0;
  var iLen = range.getNumRows();

  for(i=0; i<iLen; i++){
    s = "";

    originalAngle = Math.round(data[i][Angle]*1000)/1000;
    originalDelta = Math.round(data[i][Delta]*1000)/1000;
    newAngle = originalAngle;
    newDelta = originalDelta;
    s += "    Vb: " + data[i][Vb] + "; Original Angle: " + originalAngle + "; originalDelta: " + originalDelta;

    // if stabilator force is below target (negative Delta), increase stab angle unless at maxAngle.
    if ( newDelta < 0 && originalAngle < maxAngle ) {
      while (newAngle < maxAngle) {
        newAngle += incAngle;
        // for some reason, this may still produce a number like 1.400000003 (example only)
        newAngle = Math.round(newAngle*1000)/1000;  
        
        // set newAngle  <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< DOESN'T WORK
        data[i][Angle].setValue = newAngle;

        var response = ui.alert("c_Optimise_Stabilator", 
                                'maxAngle:' + maxAngle + 
                                "; originalAngle: " + originalAngle + 
                                "; newAngle: " + newAngle + 
                                "; data[i][Angle]: " + data[i][Angle] + 
                                "\r\n\r\nContinue?", 
                                ui.ButtonSet.YES_NO);
        if (response != ui.Button.YES) {
          break;
        }
/*
        // break when Delta becomes positive
        if (  ) break;
*/  
      } 
    }
    msg += s + "\r\n";
  }
  Logger.log("c_Optimise_Stabilator \r\n" + msg);
}
0

There are 0 best solutions below