0

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);
}
maxhugen
  • 1,870
  • 4
  • 22
  • 44
  • if you want to set the value in one cell that's the way to do it. Define a range of one cell. But usually you want to use batch operations and use setValues instead. – Marios Dec 07 '20 at 16:44
  • I am looping through the rows in 'data', doing some calcs, and then thought I could update the relevant cell in that row. How is a "batch" operation done? Should I post the entire function for you to see? – maxhugen Dec 07 '20 at 16:49
  • 1
    in your case it would be `range.setValues(data)` the down side is that if you have formulas in that range they might get messed up. So if you want to pick and choose different cells in that range then define one cell ranges and us setValue(); – Cooper Dec 07 '20 at 18:18
  • Thanks, I actually found this on another forum... also should have used data[i][Angle] = newAngle; And yes, sadly I discovered that it would remove my formulas. – maxhugen Dec 07 '20 at 18:45

0 Answers0