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
    Commented Jul 15, 2021 at 13:56

1 Answer 1

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
    Commented Jul 16, 2021 at 13:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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