Google Sheets: скрытие столбцов на основе даты в строке 1

Question

У меня нет опыта работы со сценариями в Excel или Google Sheets, поэтому я пытаюсь немного расшириться и посмотреть, есть ли решение моей проблемы. Мы используем Google Sheets для еженедельного календаря в нашем бизнесе по модернизации кухонь. Мы организуем недели слева направо и перечисляем работы, над которыми мы сейчас работаем, в этих столбцах. Я хотел бы автоматически скрывать все столбцы с датой, превышающей 4 недели, поэтому при открытии листа мы не начинаем с даты годичной давности. Я могу скрывать эти столбцы вручную каждую неделю, но когда мне нужно вернуться и посмотреть на предыдущие недели, я вынужден показать все эти столбцы, а затем выделить все столбцы, которые хочу скрыть. Наличие сценария кажется лучшим решением.

Есть ли способ запускать скрипт при каждом открытии файла, чтобы мы всегда отображали только предыдущие 4 недели и все в будущем? Если так, не могли бы вы помочь мне понять, как я мог бы написать это и заставить это работать? Опять же, я новичок, когда дело касается чего-то, кроме формул, но мне очень интересно узнать больше о возможностях сценариев.

Спасибо!

Answer 1

С помощью скрипта приложений через Tools->Script EditorВы можете создать меню с onOpen() функция Функция в меню (например, hidePast), затем необходимо будет проверить заданное значение в каждом столбце (чтобы увидеть, к какой дате относится этот столбец), а затем пометить его как скрытое или нет. onOpen Функция, потому что это "простой триггер", не может делать ничего, что требует "авторизации" (например, взаимодействие с нелокальными данными электронной таблицы), следовательно, промежуточный метод. Создав меню, вы можете упростить кому-либо использование электронной таблицы для авторизации и активации функции.

Пример:

/* @OnlyCurrentDoc */
function onOpen() {
    SpreadsheetApp.getActive().addMenu("Date Tools",
        [{name:"Hide Past", functionName:"hidePast"},
         {name:"Show All", functionName:"showAll"}]);
}
function showAll() {
    var ss = SpreadsheetApp.getActive();
    var sheet = ss.getActiveSheet();
    sheet.unhideColumn(sheet.getDataRange());
    ss.toast("All columns unhidden.");
}
function hidePast() {
    var ss = SpreadsheetApp.getActive();
    var sheet = ss.getActiveSheet();
    // Acquire the 1st row of all used columns as an array of arrays.
    var datelist = sheet.getSheetValues(1, 1, 1, sheet.getLastColumn());

    // Drop the hours, minutes, seconds, etc. from today.
    var now = new Date();
    var today = new Date(Date.UTC(now.getUTCFullYear(), now.getUTCMonth(), now.getUTCDate()));

    // Inspect the datelist and compare to today. Start from the rightmost
    // column (assuming the dates are chronologically increasing).
    var col = datelist[0].length;
    while(--col >= 0) {
        var then = new Date(datelist[0][col]);
        if(then < today) {
            break;
        }
    }

    // Bounds check, and convert col into a 1-base index (instead of 0-base).
    if(++col < 1) return;

    // col now is the first index where the date is before today.
    // Increment again, as these are 2-column merged regions (and
    // the value is stored in the leftmost range). If not incremented,
    // (i.e. hiding only part of a merged range), spreadsheet errors will occur.
    sheet.hideColumn(sheet.getRange(1, 1, 1, ++col));
    ss.toast("Hid all the columns before today.");
}

Answer 2

Поскольку у вас нет источника, похожего на базу данных, это будет очень сложно, но вы можете попробовать создать очень сложный

QUERY()

Вы должны отфильтровать даты на другом листе (и вы можете оказаться в тупике).

Поэтому я предложу использовать такую структуру, и она также позволит вам создавать другие виды фильтров (или сводные таблицы) в будущем (поддерживаемые и масштабируемые).