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!!!

Answer 1

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