How to clear only "Column A" with Google Apps Script before updating with latest content i-CodePudding

I would like to clear the contents of "column A" before "Update" function fills the latest data in the same column. The idea is remove any redundant data that is not required.

Usecase: Update all the tabs in one Index sheet, if people delete a sheet - that should not reflect in this Index sheet. Here is the code I have used after some research. I am new to this so need some help.

function onOpen() {
 
 var ui = SpreadsheetApp.getUi();

 ui.createMenu('Index Menu')
     .addItem('Create Index', 'createIndex')
     .addItem('Update Index', 'updateIndex')
     .addToUi();
}


// function to create the index
function createIndex() {
 
 // Get all the different sheet IDs
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheets = ss.getSheets();
 
 var namesArray = sheetNamesIds(sheets);
 
 var indexSheetNames = namesArray[0];
 var indexSheetIds = namesArray[1];
 
 // check if sheet called sheet called already exists
 // if no index sheet exists, create one
 if (ss.getSheetByName('index') == null) {
   
   var indexSheet = ss.insertSheet('Index',0);
   
 }
 // if sheet called index does exist, prompt user for a different name or option to cancel
 else {
   
   var indexNewName = Browser.inputBox('The name Index is already being used, please choose a different name:', 'Please choose another name', Browser.Buttons.OK_CANCEL);
   
   if (indexNewName != 'cancel') {
     var indexSheet = ss.insertSheet(indexNewName,0);
   }
   else {
     Browser.msgBox('No index sheet created');
   }
   
 }
 
 // add sheet title, sheet names and hyperlink formulas
 if (indexSheet) {
   
   printIndex(indexSheet,indexSheetNames,indexSheetIds);

 }
   
}



// function to update the index, assumes index is the first sheet in the workbook
 
function updateIndex() {
 
 // Get all the different sheet IDs
 
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheets = ss.getSheets();
 var indexSheet = sheets[0];
 
 var namesArray = sheetNamesIds(sheets);
 
 var indexSheetNames = namesArray[0];
 var indexSheetIds = namesArray[1];
 
 printIndex(indexSheet,indexSheetNames,indexSheetIds);
}

// function to clear index
function clearContentsOnly() {
var range = SpreadsheetApp
              .getActive()
              .getSheetByName("Index")
              .getRange(4,2,2,2);
range.clearContent();
}

// function to print out the index
function printIndex(sheet,names,formulas) {
 
 
 
 sheet.getRange(1,1).setValue('Task Index').setFontWeight('bold');
 sheet.getRange(7,1,formulas.length,1).setFormulas(formulas);
 
}


// function to create array of sheet names and sheet ids
function sheetNamesIds(sheets) {
 
 var indexSheetNames = [];
 var indexSheetIds = [];
 
 // create array of sheet names and sheet gids
 sheets.forEach(function(sheet){
   indexSheetNames.push([sheet.getSheetName()]);
   indexSheetIds.push(['=hyperlink("https://docs.google.com/spreadsheets/d/XXXX/edit#gid=' 
                         sheet.getSheetId() 
                         '","' 
                         sheet.getSheetName() 
                         '")']);
 });
 
 return [indexSheetNames, indexSheetIds];
 
} ```

CodePudding user response：

function updateIndex() {
 
 // Get all the different sheet IDs
 
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheets = ss.getSheets();
 var indexSheet = sheets[0]; //better to use the name of the index sheet - as the sheet position may get changed easily by the user

 indexSheet.GetRange("A2:A").clearContent(); //add this line
 
 var namesArray = sheetNamesIds(sheets);
 
 var indexSheetNames = namesArray[0];
 var indexSheetIds = namesArray[1];
 
 printIndex(indexSheet,indexSheetNames,indexSheetIds);
}

CodePudding user response：

clear contents of column A

sheet.getRange(1,1,sheet.getLastRow()).clearContent();

if you have header rows and you specify the start row:

const sr = 2;
sheet.getRange( sr, 1, sheet.getLastRow() - sr   1).clearContent();

CodePudding user response：

Make sure you create a Sheet with the name "Index" and then the following code will update the Index. It will add new sheets with the name and url below existing entries. And if a sheet has changed its name (but not its id), then it will update the sheet name.

function updateIndex(){
  const ACTIVE = SpreadsheetApp.getActive()
  const spreadsheetId = ACTIVE.getUrl()
  const currentIndexSheet = ACTIVE.getSheetByName("Index")
  
  // Assume Index Sheet has 2 Columns (Sheet Name, Sheet URL)
  const currentIndexRange = currentIndexSheet.getRange(2, 1, currentIndexSheet.getLastRow(), 2)

  // Create a Array which holds all existing Ids
  const currentIndexIds = currentIndexRange.getValues().map( row => parseInt(getSheetIdFromURL(row[1])) ).flat()
  
  // Get all Sheet Ids and Titles as [id, name]
  const sheets = ACTIVE.getSheets().map( sheet => {
    return {
      id: sheet.getSheetId(),
      name : sheet.getName()
    }
  })
  
  sheets.forEach( (sheet, index) => {
    // if the index already contains the sheet Id, then update its name
    const indexInSheet = currentIndexIds.indexOf(sheet.id)
    if( indexInSheet != -1 ){
      console.log("Found and replacing title")
      currentIndexSheet.getRange(indexInSheet 1, 1).setValue(sheet.name)
      return
    }
    
    // if its not in the sheet, append it
    currentIndexSheet.appendRow( [ sheet.name, generateUrlFromId(sheet.id) ] )
  }) 

   function getSheetIdFromURL( url ){ return url.match(/\d $/g)}
  
   function generateUrlFromId( id ){ return `https://docs.google.com/spreadsheets/d/${spreadsheetId}/edit#gid=${id}` }

}