CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Giel Toonders
Giel Toonders

Reputation: 5

Google scripts: How to put a range in setValue

I have used the example code from this link, to make the code below. https://yagisanatode.com/2017/12/13/google-apps-script-iterating-through-ranges-in-sheets-the-right-and-wrong-way/

Most of the code is working as expected, except for the last row.

In Column E, I want to place the custom function =apiav() with the data from cell A. However the code is returning =apiav(Range) in the Google sheet cells. (to be clear it should be something like =apiav(a1))

I tried everything i could think of and of course googled for hours, but i am really lost and can't find the right solution for this.

function energy(){
  var sector = "Energy";
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var rangeData = sheet.getDataRange();
  var lastColumn = 2;
  var lastRow = 999 ;
  var sheet = ss.getSheets()[0];     
  var searchRange = sheet.getRange(2,2, lastRow-1 ,1 );
  var ouputrange = sheet.getRange(2,4, lastRow-1 ,1 );
  //clear range first
  ouputrange.clear("D:D");
   ouputrange.clear("E:E");
      /*
      GOOD - Create a client-side array of the relevant data
      */
        // Get array of values in the search Range
        var rangeValues = searchRange.getValues();
        // Loop through array and if condition met, add relevant
        // background color.
        for ( i = 0; i < lastColumn - 1; i++){
          for ( j = 0 ; j < lastRow - 1; j++){
            if(rangeValues[j][i] === sector){
              sheet.getRange(j+2,i+4).setValue("yes");
              var formularange = sheet.getRange (j+2,i+1);
              sheet.getRange(j+2,i+5).setValue('=apiav(' + formularange + ')');
             }
          };
          };
};

Upvotes: 0

Views: 210

Answers (1)

Marios
Marios

Reputation: 27348

Replace:

var formularange = sheet.getRange(j+2,i+1);

with:

var formularange = sheet.getRange(j+2,i+1).getA1Notation();

So you will be able to pass the cell reference instead of the range object.

Upvotes: 2

Related Questions