Javascript - Google Sheets script to clear active sheet without clearing formulas or Letters,

marcOstrensky
December 17, 2024
77 views
0 votes
2 Answers

I have written this script to clear data and keep formulas, however I would like to keep
the letters. IE column titles I have made so that when I enter data I know where it goes.

This is my current script:

  function onOpen(e) {
    SpreadsheetApp.getUi().createMenu('Utills')
   .addItem("Entire current sheet", 'entireSheet')
   .addItem('Current active range', 'activeRange')
   .addToUi();
  }

  function entireSheet() {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = ss.getActiveSheet();
    const range = sheet.getDataRange();
    keepFormulas(range);
  }

   function activeRange() {
     const ss = SpreadsheetApp.getActiveSpreadsheet();
     const sheet = ss.getActiveSheet();
     const range = sheet.getActiveRange();
     keepFormulas(range);
   }

   function keepFormulas(range) {
     const formulas = range.getFormulas();
     range.clearContent();
     range.setFormulas(formulas);
   }

I am completely stumped on how to achieve this.

Answers

- QueensCoder
- December 17, 2024 at 12:52 am
- 0 votes
0
You can target a range that excludes row 1. This means the range you’d pass to the keepFormulas function would be from row 2 and onwards. This will preserve row 1 and let you keep your titles
```
const lastRow = sheet.getLastRow();
const lastColumn = sheet.getLastColumn();

// Adjust the range to start at row 2 and include all columns
const rangeWithoutHeaderRow = sheet.getRange(2, 1, lastRow - 1, lastColumn); 
// Start at 2nd Row, 1st Column
sheet.setActiveSelection(range);
```
Login or Signup to reply.

Exclude Header row from clearing

To exclude the header row from being cleared, you should specify a custom range using various methods from Apps Script’s Class Range like getLastRow, getLastColumn etc. This modification achieves the goal of clearing the text while preserving the formulas and the header row.

Code

function onOpen(e) {
  SpreadsheetApp.getUi().createMenu('Utills')
    .addItem("Entire current sheet", 'entireSheet')
    .addItem('Current active range', 'activeRange')
    .addToUi();
}

function entireSheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const lastRow = sheet.getLastRow();
  const lastcolumn = sheet.getLastColumn();
  
  const range = sheet.getRange(2, 1, lastRow - 1, lastcolumn);
  keepFormulas(range);
}

function activeRange() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const range = sheet.getActiveRange();
  
  const startRow = range.getRow();
  const startCol = range.getColumn();
  const numRows = range.getNumRows();
  const numCols = range.getNumColumns();
  
  const newRange = (startRow === 1) ? 
    sheet.getRange(2, startCol, numRows - 1, numCols) : 
    range;
    
  keepFormulas(newRange);
}

function keepFormulas(range) {
  const formulas = range.getFormulas();
  range.clearContent();
  range.setFormulas(formulas);
}

Sample Outout

Before running the script