Google Sheets | AppScript | Move Selected Range Based on Input Value, Skipping Every Nth Column(s)

Question

I'd like to write a function that, when called, moves the selected range to the right a distance determined by a user inputted number (a dialogue box prompted by the function), whilst ignoring certain columns (both from the selected range and the output range).

In this photo, I've attempted to illustrate two examples of how this function would operate. The 'Original' and 'Result' rows are intended to be the same row, so this function would theoretically rewrite values within the selected range and to the right of it.

The primary difference between this example screenshot and our actual, working spreadsheet is that the column header numbers (rather than being 1,2,3,4,5,6,7), are weekdays and weekends.

This function would not require reading a header value in a column, it could just read as col()+1, but I wanted to illustrate how the values would need to be offset by frozen/protected columns on the left (A:A).

This operation is similar to what project management programs would simulate when showing durations that ignore/skip weekends (such as sliding a duration on a gantt chart). I understand there are ways to reorganize this data that would make the function much easier to write (such as removing weekends from the spreadsheet), but unfortunately the spreadsheet that needs this function is a shared one, and thus my options for automating this tedious task are limited to this rather obtuse math problem that I have not been able to find a solution for thus far.

Link to Example Sheet

Answer 1

Note: This is just a script that you could use as a starting point or reference for your project. Here in the community, we do not code for you, especially if you haven't tried anything on your own to approach this first. Obviously, there are still some limitations to this logic, but it will give you an idea of how you can approach this matter.

This matter can basically be achieved by using the JavaScript Date getDay() method to determine the type of day per range header. Afterward, you can utilize the unshift and splice methods to manipulate the data and skip the weekend days.

Sample

var row;
var startColumn;

function onOpen() {
  SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
      .createMenu('Custom Menu')
      .addItem('Show prompt', 'showPrompt')
      .addToUi();
}

function showPrompt() {
  var ui = SpreadsheetApp.getUi(); // Same variations.

  var result = ui.prompt(
      'Add an input',
      'Input a number:',
      ui.ButtonSet.OK_CANCEL);

  // Process the user's response.
  var button = result.getSelectedButton();
  var text = result.getResponseText();
  if (button == ui.Button.OK) {
    // User clicked "OK".
    checking(text);
  } 
}

function checking(input) {
  var ss = SpreadsheetApp.getActive().getActiveSheet();
  var data = ss.getActiveRange().getDisplayValues();
  var structuredData = data.map(x => x.map(y => [y])).flat();

  //add the input at the beginning of the array data
  for (var x = 1; x <= input; x++) {
    structuredData.unshift(['']);
  }

  //place the result next to the original selection
  row = ss.getActiveRange().getRow() + 1;
  startColumn = ss.getActiveRange().getColumn();

  //Place the intial result
  ss.getRange(row, startColumn, 1, structuredData.length).setValues([structuredData.flat()]).activate();

  //Analyze the intial result
  analyzer(ss, structuredData.flat())
}

function analyzer(ss, array) {
  var rawSheetData = ss.getRange(ss.getActiveRange().getA1Notation().replace(/\d/gm, '1')).getValues().flat().map((x, i) => [new Date(x).getDay(), i + 2, array[i]]);

  //Log for review & debugging
  console.log(`Showing result in row: ${row}`)
  console.log(`Structue of the \"rawSheetData\"`, `\n[weekday,column number,cellData]`);
  console.log(rawSheetData)

  var check = rawSheetData.filter(x => (x[0] == 0 || x[0] == 6) && !x[2] == '');

  //0 (sun) plaus add 1 column & 6 (sat) add 2 columns
  try {
    check.find(x => x[0] === 6).length > 0 ? rawSheetData.splice(rawSheetData.map((x, i) => x[0] == 6 && (!x[2] == '') ? i : null).filter(x => x)[0], 0, '', '') : null
  } catch {
    rawSheetData.splice(rawSheetData.map((x, i) => x[0] === 0 ? i : null).filter(x => x)[0], 0, '');
  }

  ss.getRange(row, startColumn, 1, rawSheetData.map(x => x[2]).length).setValues([rawSheetData.map(x => x[2])]).activate();
}

Result

Resources:

• Google Spreadsheet Prompt dialogs
• JavaScript Date getDay()
• JavaScript unshift() array method
• JavaScript splice() array method