0

I have a script from users Iamblichus and Cooper which creates a list of sheetnames and updates the list as the names are edited or new ones are created. The script does very well copying the data over however the issue is this script erases the formatting of the cells where the list is copied to. Any help gladly appreciated!

Current working function:

function createOnChangeTrigger() {  
  const ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger("writeSheetNames")
  .forSpreadsheet(ss)
  .onChange()
  .create();
}



function writeSheetNames() {
  const excl = ['Sheet1','Sheet2'];//sheet names to exclude
  const ss = SpreadsheetApp.getActive();
  const sheetNames = ss.getSheets().map(sheet => [sheet.getName()]).filter(n => 
!~excl.indexOf(n[0]));
  const sheet = ss.getSheetByName("Sheet24");
  sheet.getRange("A:A").clear(); // Delete previous data
  sheet.getRange(1,1,sheetNames.length).setValues(sheetNames);
}

(first function creates the trigger, second function does the action)

1
  • 1
    No particular knowedge of google-sheets but in excel, it would be by adding 'contents' to the clear statement, as in '.clearcontents'. There is likely something similar with their service. – Mockman Jul 15 at 13:56
0

Instead of .clear() use .clearContent()

The above because as you have seeing, the first removes everything from the range, while the second will remove only the range values.

1
  • Thank you! Worked perfectly, best -Joseph – jbaker Jul 16 at 13:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.