Clear content of a cell and then set a new value

I´m trying to clear the content of a cell and then set a new value. I know you´ll ask "why not just set a new value instead of clearing the cell first?", but I really need it to be that way. Here´s what I have

var ss = SpreadsheetApp.getActiveSheet();
var new = ss.getRange("G1");
new.clearContent();
new.setValue('|');  

the problem is that the clearing is not happening, I tried a lot of things but none of them worked :S. Does anybody know what I´m doing wrong? Thanks in advance!

Upvotes: 2

Views: 9093

Answers (1)

Diego
Diego

Reputation: 9571

You can't use new as it is reserved. Use something else like cell. Also, using getActiveSheet() is dangerous because it requires that you actually have that sheet selected. So if you want to modify something in "Sheet1", but your cursor is on a cell in "Sheet2", then you'll get an unexpected result. Instead, clearly specify which sheet you want to use.

function test() {
  var ss = SpreadsheetApp.getActive().getSheetByName("SHEET NAME");
  var cell = ss.getRange("G1");
  cell.clearContent();
  cell.setValue('|'); 
}

To prove that this does clear the cell first, you can use SpreadsheetApp.flush() in conjunction with Utilities.sleep(). You'll need to use flush() because, "Spreadsheet operations are sometimes bundled together to improve performance". Calling flush() will make sure that all those operations are executed before continuing with the script.

function test() {
  var ss = SpreadsheetApp.getActive().getSheetByName("SHEET NAME");
  var cell = ss.getRange("G1");
  cell.clearContent();
  SpreadsheetApp.flush();
  Utilities.sleep(10000); // You have 10 seconds to check that the cell has cleared
  cell.setValue('|'); 
}

Upvotes: 5

Related Questions