Reputation: 73

How to set formula with condition if the cell is blank with google app script?

So I have multiple files that have a column where I would like to update in the formula. However, there might be a certain cell that already has a value in it, but I don't want to replace it with the formula (see screenshot for reference).

I read some references here, but haven't found a similar case like mine.

This is the attempt that I do, but it's not working:

function updateWithFormula(){

/*** Input Data From Multiple Sources ****/

var sourceWorkbook = SpreadsheetApp.openById('')//id of the workbook

//Open tab 'Sheet1' and pull the data inside the script
var sourceSheet = sourceWorkbook.getSheetByName('Sheet1')
var source = sourceSheet.getDataRange().getDisplayValues()


  for(row in source){ 

    if (source[row][3]=="Update Value") {
    
    //open files through link
    var files = SpreadsheetApp.openByUrl(source[row][2]) //there's a link inside this column that linked to the file that I want to update
    
    /*******insert formula *******/
    
    //get range that want to be inserted by the formula, which is column S
    //if the column S already have value in it, I don't want to do anything in it, however if it doesn't have value, I would like to put a formula

    var result = files.getSheetByName('Sheet1').getRange("S2:S") //this is the column that I want to update

    //set formula
    for(r in result)
    {
      if(result[r] == "")
       result[r].setFormula("=R"+ r+1)
    }



    }

  }


}

Do you guys have any idea why my code is not working? Any advice for this case?

Thank you!

Upvotes: 0

Answers (2)

Tanaike

Reputation: 201338

I believe your current situation and your goal are as follows.

"Sheet1" of sourceWorkbook has the Spreadsheet URLs and the value of "Update Value" in the columns "C" and "D", respectively.
You want to retrieve the Spreadsheet from the URL, and want to check the column "S2:S" of of "Sheet1" in the retrieved Spreadsheet, and want to put a formula like "=R"+ r+1 to the non-empty cells of the column "S".

In this case, how about the following modification?

Modification points:

var result = files.getSheetByName('Sheet1').getRange("S2:S") returns Class Range object. This cannot be used with for(r in result). This is the reason of but it's not working. This has already been mentioned by the Oriol Castander's answer.
When setFormula is used in a loop, the process cost becomes high.

When these points are reflected in your script, it becomes as follows.

Modified script:

function updateWithFormula() {
  var sourceWorkbook = SpreadsheetApp.openById(''); // Please set your Spreadsheet ID.

  var sourceSheet = sourceWorkbook.getSheetByName('Sheet1');
  var source = sourceSheet.getDataRange().getDisplayValues();
  source.forEach(r => {
    if (r[3] == "Update Value") {
      var sheet = SpreadsheetApp.openByUrl(r[2]).getSheetByName("Sheet1");
      var rangeList = sheet.getRange("S2:S" + sheet.getLastRow()).getDisplayValues().flatMap(([e], i) => e == "" ? [`S${i + 2}`] : []);
      if (rangeList.length > 0) {
        sheet.getRangeList(rangeList).setFormulaR1C1("=R[0]C[-1]");
      }
    }
  });
}

In this modification, the formula is put as the R1C1 using the range list. By this, I thought that the process cost will be able to be reduced a little.

References:

Upvotes: 1

Oriol Castander

Reputation: 648

Objective

If I understood correctly, your objectives are the following:

Retrieve data from a "master" spreadsheet with information on which spreadsheets to update.
Loop through said data and locate the spreadsheets (represented as rows) that require updating.
Open those spreadsheets individually.
Update those spreadsheets rows with a sheets formula if a certain condition is met (in this case, that the cell is blank).

Issues

The for(var a in b) syntax in javaScript is used to iterate through object, not arrays. You should change it to:

for (var i = 0; i<source.length; i++){
//YOUR CODE
}

where: source[i] lets you access that specific row.

When you try to get the individual sheets' values, you are actually only getting the range, not the values themselves. You should replace this:

var result = files.getSheetByName('Sheet1').getRange("S2:S")

with this:

var sheet = files.getSheetByName('Sheet1');
var range = sheet.getRange("S2:S");
var values = range.getValues();

(You can read more about ranges and how they work here).

To input values into a spreadsheet, you should do it by using the setValue() method in the range class. Again, go here for more info. So, instead of:

 result[r].setFormula("=R"+ r+1)

use:

var rangeToModify = sheet.getRange(j, 19); //LETTER S IS THE 19TH
rangeToModify.setValue("=R"+ (j+1)); //SET THE FORMULA

Final Code

function updateWithFormula(){
  var sourceWorkbook = SpreadsheetApp.openById('')//id of the workbook
  
  //Open tab 'Sheet1' and pull the data inside the script
  var sourceSheet = sourceWorkbook.getSheetByName('Sheet1')
  var source = sourceSheet.getDataRange().getDisplayValues()
  
  for(var i = 0; i<source.length; i++){
    if (source[i][3]=="Update Value"){

      var files = SpreadsheetApp.openByUrl(source[row][2]);

      var sheet = files.getSheetByName('Sheet1');
      var range = sheet.getRange("S2:S");
      var values = range.getValues();

      //set formula
      for(var j = 0; j<values.length; j++){
        if (values[j] == ""){

          //GET THE RANGE THAT YOU WANT TO MODIFY
          var rangeToModify = sheet.getRange(j, 19); //LETTER S IS THE 19TH
          rangeToModify.setValue("=R"+ (j+1)); //SET THE FORMULA
        }
      }
    }
  }
}