CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
jlmscad
jlmscad

Reputation: 39

Updating a range of cells in google sheets when one cell is changed using

Using JavaScript, I want to update a range of cells in Google Sheets. The code doesn't throw any error, but doesn't work either.

function OnEditDate(e){    
    var app = SpreadsheetApp;  
    var ss = app.getActiveSpreadsheet();  
    var act = ss.getActiveSheet();
    var i;
    var j;

    for(i = 2; i = 30; i++){
        for(j = 5; j = 20; j++){
            var newVal = act.getRange(i+1,j).getValue();
            act.getRange(i,j).setValue(newVal);

        }
    }
}

This should update all cell in range E2:U30 by moving the contents of all cells to the left one cell.

Upvotes: 1

Views: 485

Answers (1)

chuckx
chuckx

Reputation: 6947

Let's start off with a few observations...

Your for loops are not specified correctly.

The for loop syntax is for ([initialization]; [condition]; [final-expression]).

Your condition statements are i = 30 and j = 20, which are assignment statements. So in addition to clobbering your intended logic, they also always evaluate to true. You should instead be using a comparison operator, something like i >= 30 and j >= 30.

Furthermore, you shouldn't call API methods such as getRange(), getValue(), setValue() within for loops. These operations are slow and will cause the script to take a long time to complete.

You should use batch operations to get/set the entire range of cells you'd like to update in as few API calls as possible.

In your case, since you're not processing the data, you have no need to actually loop through the data.

Here's an implementation that performs the move you described:

function moveCells() {
  var sheet = SpreadsheetApp.getActiveSheet();

  var startingColumn = 5;  // Column E
  var startingRow = 2;  // Row 2
  var numColumns = 17;  // to Column U
  var numRows = 29;  // to Row 30

  // Get the source range which contains the data.
  var sourceRange = sheet.getRange(startingRow, startingColumn, numRows, numColumns);

  // Get the target range (i.e. same size as sourceRange, but starting one column
  // to the left).
  var targetRange = sheet.getRange(startingRow, startingColumn - 1, numRows, numColumns);

  // Get all of the values from sourceRange into a 2-dimensional array.
  var values = sourceRange.getValues();

  // Clear all of the cells in sourceRange.
  sourceRange.clearContent();

  // Set the cell values targetRange, using the values extracted from sourceRange.
  targetRange.setValues(values);
}

Note that I'm using the 4 argument version of getRange(), which allows you to work with a grid of cells.

Similarly, I'm using getValues() and setValues(), which allows you to get and set the entire grid of cells in one operation.

Upvotes: 1

Related Questions