CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-api
PatMac624
PatMac624

Reputation: 11

How do I clear data only but not formulas on google sheets?

I am building a daily checklist that uses a trigger to make a new tab every night at 12pm. The code is below - but I need the code to clear the content of those cells, not the underlying formulas. I assume instead of "var rangetoclear" it must be something else, but I could not find on help page.

Can anybody help?

Here is the code:

/* DASHBOARD---------------------------------------------------------------------------------------------------
  Edit this section to change the dates tabs are created for and the range of data that will be cleared each time! */
  
  var numberOfDaysForwardForNextTab = 0
  var rangeToClear = 'F8:I1003'
  
  // -------------------------------------------------------------------------------------------------------------
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();   // get the spreadsheet object
  SpreadsheetApp.setActiveSheet(ss.getSheets()[0]); // set the first sheet as active

  // Sets date for add tab & date for delete tab
  var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
  var today = new Date();
  var addWeek = Utilities.formatDate(new Date(today.getTime() + (numberOfDaysForwardForNextTab * MILLIS_PER_DAY)), "GMT+1", "MM-dd-yy"); 
  
  // Adds tab
  ss.duplicateActiveSheet(); //Copies current sheet
  ss.renameActiveSheet(addWeek); //Renames sheet to date from above
  ss.moveActiveSheet(1); //Moves sheet to the first position

  // Prepares tab (clears old content)
  var sheet = ss.getSheetByName(addWeek);
  sheet.getRange(rangeToClear).clearContent()
}

Upvotes: 0

Views: 4546

Answers (2)

Edward Cariello
Edward Cariello

Reputation: 1

sheet.getRange(rangeToClear).clear({contentsOnly: true});

Upvotes: 0

Marios
Marios

Reputation: 27348

One solution is to get the formulas of the source sheet and for the range you want to clear rangeToClear and then copy the formulas to the duplicate sheet you just created after clearing its contents:

  const rangeToClear = 'F8:I1003';
  const source_sheet = ss.getSheets()[0];
  const formulas = source_sheet.getRange(rangeToClear).getFormulas();
  const sheet = ss.getSheetByName(addWeek);
  sheet.getRange(rangeToClear).clearContent()
  sheet.getRange(rangeToClear).setFormulas(formulas)

References:

Upvotes: 2

Related Questions