CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Sheets-User
Sheets-User

Reputation: 1

Declaring current cell variable incorrectly in google apps script

I am pretty experienced with writing formulas in Google Sheets to allow the automation of data inputted from Google Forms.

I am trying to build on this now by learning Apps Script. I am trying to write a script that will

  1. Start when the sheet is opened
  2. Run continuously in the background, checking whether the current cell is A1. (I am attempting this by using a while loop, for a condition that will always be true.)
  3. Delete the contents of F1

At the moment, the code is running no matter what cell is active. From debugging I think that this is because the if statement isn't written correctly, and also that the variable CurrentCell isn't being declared properly.

This is the code that I have at present:

    function DeleteCell() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var num1 = 1


  // run the following loop
  do {


    // add 1 to num1
    num1++;

var CurrentCell = sheet.getCurrentCell();    
    // if the current cell is A1
    if(CurrentCell==1,1)
       {

  sheet.getCurrentCell().offset(0, 5, 1, 1).activate();
  sheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  var CurrentCell = sheet.getCurrentCell()
       }

  }while (num1 > 5); // continue running the loop while num1 is greater than five


};

Upvotes: 0

Views: 152

Answers (1)

Cooper
Cooper

Reputation: 64100

Perhaps this is want meant:

function clearRange() {
  var ss=SpreadsheetApp.getActive();
  var sheet=ss.getActiveSheet();
  var num1=1;
  do {
    num1++;
    var CurrentCell=sheet.getCurrentCell();//Current cell is a range    
    if(CurrentCell.getA1Notation()=='A1') {
      sheet.getCurrentCell().offset(0, 5, 1, 1).clear({contentsOnly: true, skipFilteredRows: true});
    }
  }while(num1<5);
}

Unfortunately, it isn't going to run in the background and if your let run it will timeout at some point and the amount out time it ran will be substracted from your daily quota.

I'm not sure what you're trying to accomplish but my guess is that it might be better to do it with an onEdit trigger.

triggers

There aren't very many reasons to use activate in most scripts. They use them in macros because they are essentially following each step that you take. When you run scripts you don't really want to stop between each step and if you did you could inform the user a lot better with a modal dialog.

Upvotes: 1

Related Questions