Script to hide and unhide non consecutive columns in google sheets

Question

I'm working on a financial forecast vs Actuals. I created a button to separate forecast months vs. actual months and need a script to make them work. I want too be able to click forecast button, and show only forecast columns, or click actuals and only show actual columns. However I can't find script that hides non consecutive columns.

I tried recording a micro (hiding all columns I did not want to show) but its not working when I try to assign script to button.

So, I need help getting script where I can hide columns that are not consecutive. For example, hide Column 12, 14, 16, 18, 20, 22, 24, 26, 28...in order to only show forecast and not actuals. I'm hoping to assign this script to forecast button. I would need to repeat the same for the actual button; where I hide all forecast column so that I'm left only with actual columns.

I attach photo of how columns are set up (Jan Forecast, Jan Actuals, Feb Forecast, Feb Actuals...) 1st Half of spreadsheet, could not fit entire sheet of photo 2nd Half of spreadsheet, could not fit entire sheet of photo

I also tested below script I found online, but it shows: Attempted to execute myFunction, but could not save.

function myFunction() {
  const hideColumns = [12, 14, 16, 18];
  const showColumns = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 13, 15, 17];
  const sheetName = "1IkwLljOl3zeH8VNKnb4iWXuEllojgXeJ1uNcLzLCykc";

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheetId = ss.getSheetByName(1IkwLljOl3zeH8VNKnb4iWXuEllojgXeJ1uNcLzLCykc).getSheetId();
  const requests = [];
  
  // Create requests for the hide columns.
  if (hideColumns.length > 0) {
    hideColumns.forEach(c => 
      requests.push({ updateDimensionProperties: { properties: { hiddenByUser: true }, range: { sheetId: sheetId, dimension: "COLUMNS", startIndex: c - 1, endIndex: c }, fields: "hiddenByUser" } })
    );
  }

  // Create requests for the show columns.
  if (showColumns.length > 0) {
    showColumns.forEach(c =>
      requests.push({ updateDimensionProperties: { properties: { hiddenByUser: false }, range: { sheetId: sheetId, dimension: "COLUMNS", startIndex: c - 1, endIndex: c }, fields: "hiddenByUser" } })
    );
  }

  // Request to Sheets API using the created requests.
  if (requests.length > 0) Sheets.Spreadsheets.batchUpdate({requests: requests}, ss.getId());
}

Help!!!

About your current issue of I also tested below script I found online, but it shows: Attempted to execute myFunction, but could not save., if you are actually trying to use tyour showing script, 1IkwLljOl3zeH8VNKnb4iWXuEllojgXeJ1uNcLzLCykc of const sheetId = ss.getSheetByName(1IkwLljOl3zeH8VNKnb4iWXuEllojgXeJ1uNcLzLCykc).getSheetId(); might be the reason for your current issue. If you want to useconst sheetName = "1IkwLljOl3zeH8VNKnb4iWXuEllojgXeJ1uNcLzLCykc";. Please modify it to const sheetId = ss.getSheetByName(sheetName).getSheetId();. — Tanaike
– Tanaike, Commented Oct 20, 2023 at 5:00

ohmyqt · Accepted Answer · 2023-10-20 06:09:26Z

0

hideColumn / unhideColumn methods will work just fine for this task. Just replace const sheetName = "Sheet1" with your actual sheet name.

This function is less complicated than batchUpdate.

function myFunction() {
  const hideColumns = [12, 14, 16, 18];
  const showColumns = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 13, 15, 17];
  const sheetName = "Sheet1";

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const requests = [];
  
  // Create requests for the hide columns.
  if (hideColumns.length > 0) {
    hideColumns.forEach(hide=>{
        sheet.hideColumn(sheet.getRange(1,hide))
    })
  }

  // Create requests for the show columns.
  if (showColumns.length > 0) {
    showColumns.forEach(show=>{
        sheet.unhideColumn(sheet.getRange(1,show))
    })
  }
}

answered Oct 20, 2023 at 6:09

ohmyqt

715 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Script to hide and unhide non consecutive columns in google sheets

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related