CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Digital Farmer
Digital Farmer

Reputation: 2107

Clear values from a range that does not specify the total number of rows

I'm trying to clear the values of the Clima!V1:V range, but it's giving an error because I don't specify the total size of the range, what is the simplest way to solve this problem? Taking into account that the total amount of rows in this column can change according to the time.

function ApagarHistoricoDoClima() {
  var ss = SpreadsheetApp.getActive();
  ss.getRange('Clima!V1:V').clear({contentsOnly: true, skipFilteredRows: true});
}

Upvotes: 0

Views: 41

Answers (2)

Cooper
Cooper

Reputation: 64072

const range = sheet.getRange(1, 22, sheet.getLastRow());

Upvotes: 0

Lle.4
Lle.4

Reputation: 616

I would reccomend using the Sheet.getRange(row, column, numRows) function from the Spreadsheets library. So, I would use the following code:

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('Clima');
const range = sheet.getRange(1, 22, sheet.getMaxRows());
range.clear({contentsOnly: true, skipFilteredRows: true});

getMaxRows is a function documented here.

EDIT:

Per the request by the question poster, I have made a simplified version of this script, more easily accessable to a person with less programming experience:

// USER: START EDITING HERE

const sheetName = 'Clima';
const column = 'V';
const startRow = 1;

// USER: DO NOT EDIT BELOW THIS LINE

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(sheetName);
const lastRow = sheet.getMaxRows();
const range = sheet.getRange(column + startRow + ':' + column + lastRow);
range.clear({contentsOnly: true, skipFilteredRows: true});

As is shown in the code, the user should only edit the three constants, and then does not have to worry about the below. If you did not want startRow to be something that can be modified, you could move that below the user variables. I will leave it to you to explain to end users how to modify the three variables.

Also, per the second answer to this question, you can use getLastRow instead of getMaxRows in the script. getLastRow gets the last row with content in the sheet, whereas getMaxRows gets the actual last row in the entire sheet.

Upvotes: 1

Related Questions