CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Adithya Shreshti
Adithya Shreshti

Reputation: 55

How to clear only "Column A" with Google Apps Script before updating with latest content in the same column

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.

EDIT: Also how to exclude certain "Sheets" from the "Update" function so it doesn't show up in the Index column?

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];
 
} ```

Upvotes: 0

Views: 968

Answers (3)

Neven Subotic
Neven Subotic

Reputation: 1429

Make sure you create a Sheet with the name "Index" and then the following code will update column A with all Sheets and directly link to the respective sheet. 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 spreadsheetURL = ACTIVE.getUrl()
    const currentIndexSheet = ACTIVE.getSheetByName("Index")
  
    // Empty the sheet
    currentIndexSheet.getRange("A1:A").clearContent()

    // We will populate this with all rows
    let outputRows = []

    // List Sheets which should not be listed
    let skipSheets = ["Index", "Another Sheet to Skip"]
    
    // Add Header Row
    outputRows.push(['="Linked Sheet"'])
    
    // Get all Sheet and add them to the outputRows using a Hyperlink
    ACTIVE.getSheets().forEach( sheet => {

      // Skip certain sheets which are defined above
      if( skipSheets.indexOf(sheet.getName()) != - 1) return  

      outputRows.push([`=HYPERLINK("${spreadsheetURL}#gid=${sheet.getSheetId()}", "${sheet.getName()}")`])  
    })

    // Write everything to Index
    currentIndexSheet.getRange(1,1, outputRows.length).setFormulas(outputRows)
}

Upvotes: 0

Cooper
Cooper

Reputation: 64040

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();

Upvotes: 1

arul selvan
arul selvan

Reputation: 624

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);
}

Upvotes: 0

Related Questions