Google Sheets Script - For each cell in range: check if each cell is blank, if not blank: separately-CodePudding

I am struggling with how to phrase this (probably quite basic) function, though I believe my use case may differ enough to justify asking - if not, please feel free to correct me or point me towards an appropriate resource.

My goal is to iterate through each cell in range B36:B38 on Sheet1, and for each cell that is not blank in that range, call a separate function and pass the cell value to it. This would ideally make it so the function will be run for every cell in the range that has a value - if the first and third cells have a value but the second cell does not have a value, the value of the first cell will be passed on to the function and the function will run, and when that function completes, the value of the third cell will be passed to the function and the function will run again. I feel I am close with the below code but I am unsure where I'm going wrong. Any insights would be helpful.

var spreadsheet = SpreadsheetApp.getActive();
var searchRange = spreadsheet.getSheetByName('Sheet1').getRange('B36:B38').getValues();    

for (i=0; i < searchRange.length; i  ) {
    if (searchRange.values !== null) {
      var variableToPassOn = "testing "   searchRange[0];
      runFunction(variableToPassOn)
    }

  }

CodePudding user response：

Calls from and to the sheet are time consuming, so it is faster to store all the values in the script and then push to the sheet in one call. For reference it would be wise to dive deeper into JavaScript array methods.

function setValueToEmptyCell(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Sheet1');
  const valuesFlat = sheet.getRange('B36:B38').getValues().flat();
  //Empty array to store the output values;
  const output = [];

  //For each row we check if it is blank, if true then push the testing value to the output array, if not the original value
  valuesFlat.forEach(row => {
    if(row == ''){
      output.push(['Testing2']);
    } else {
      output.push([row])
    }
  })

  //Because we have stored the original value we overwrite the hole range with the 'new' values
  sheet.getRange('B36:B38').setValues(output);
}