Script, Macro for Google Sheets Access in All Books

Desktop Apps, Javascript and Typescript — incorrectly specified categories?

Project translated automatically. Log in or register, to view the original I’m looking for a master of GoogleSheets, which is understood on macros and AppsScript. The task. There are 30 Google tables with data. There is a macro that creates 4 unnecessary format tables. The task is to make possible the launch of this script in all the tables.

Update #1 from 5 June 2023

Переписати скрипт який був записаний як макрос і що вказано нижче,

щоб він був виконаний для кожної таблиці в певній папці на Google Диск

і результатом був такий самий як у прикладі. Приклад резултату скрипта https://docs.google.com/spreadsheets/d/18iAH6Q6nhx4Ph6yxytofYgSm4lIf7RuaeXU9m7FdzYM/edit?usp=sharing

Створено 3 нових аркуша, з трьома зведеними таблицями різного формату.

Скрипт буде запускатися з AppsScript. (або інакше, якщо є більш простий варіант)

В папці тільки гугл таблиці. Всі таблиці мають однаковий формат., однакову назву першого листа для обробки.

Скрипт який треба переписати :

var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getActiveSheet();
sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).activate();
var sourceData = spreadsheet.getRange('1:1000');
spreadsheet.insertSheet(spreadsheet.getActiveSheet().getIndex() + 1).activate();
spreadsheet.getActiveSheet().setHiddenGridlines(true);
var pivotTable = spreadsheet.getRange('A1').createPivotTable(sourceData);
pivotTable = spreadsheet.getRange('A1').createPivotTable(sourceData);
var pivotGroup = pivotTable.addRowGroup(11);
pivotTable = spreadsheet.getRange('A1').createPivotTable(sourceData);
var pivotValue = pivotTable.addPivotValue(32, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
pivotGroup = pivotTable.addRowGroup(11);
pivotTable = spreadsheet.getRange('A1').createPivotTable(sourceData);
pivotValue = pivotTable.addPivotValue(32, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
pivotGroup = pivotTable.addRowGroup(11);
pivotGroup.sortBy(pivotTable.getPivotValues()[0], []);
pivotTable = spreadsheet.getRange('A1').createPivotTable(sourceData);
pivotValue = pivotTable.addPivotValue(32, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
pivotGroup = pivotTable.addRowGroup(11);
pivotGroup.sortDescending()
.sortBy(pivotTable.getPivotValues()[0], []);
spreadsheet.getRange('B:B').activate();
spreadsheet.getActiveRangeList().setNumberFormat('#,##0.00')
.setNumberFormat('#,##0.0')
.setNumberFormat('#,##0');
spreadsheet.getRange('A3').activate();
spreadsheet.getActiveSheet().setColumnWidth(1, 609);
spreadsheet.getRange('A:A').activate();
spreadsheet.getActiveRangeList().setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP);
spreadsheet.getActiveSheet().setName('Зведена по Клієнтам');
spreadsheet.duplicateActiveSheet();
spreadsheet.getActiveSheet().setName('Зведена по Клієнтам/Товарам');
pivotTable = spreadsheet.getRange('A1').createPivotTable(sourceData);
pivotValue = pivotTable.addPivotValue(32, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
pivotGroup = pivotTable.addRowGroup(11);
pivotGroup.sortDescending()
.sortBy(pivotTable.getPivotValues()[0], []);
pivotGroup = pivotTable.addRowGroup(27);
pivotTable = spreadsheet.getRange('A1').createPivotTable(sourceData);
pivotValue = pivotTable.addPivotValue(32, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
pivotGroup = pivotTable.addRowGroup(11);
pivotGroup.sortDescending()
.sortBy(pivotTable.getPivotValues()[0], []);
pivotGroup = pivotTable.addRowGroup(27);
pivotGroup.sortDescending();
pivotTable = spreadsheet.getRange('A1').createPivotTable(sourceData);
pivotValue = pivotTable.addPivotValue(32, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
pivotGroup = pivotTable.addRowGroup(11);
pivotGroup.sortDescending()
.sortBy(pivotTable.getPivotValues()[0], []);
pivotGroup = pivotTable.addRowGroup(27);
pivotGroup.sortDescending()
.sortBy(pivotTable.getPivotValues()[0], []);
pivotTable = spreadsheet.getRange('A1').createPivotTable(sourceData);
pivotValue = pivotTable.addPivotValue(32, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
pivotGroup = pivotTable.addRowGroup(11);
pivotGroup.sortDescending()
.sortBy(pivotTable.getPivotValues()[0], []);
pivotGroup = pivotTable.addRowGroup(27);
pivotGroup.sortDescending()
.sortBy(pivotTable.getPivotValues()[0], []);
pivotGroup = pivotTable.addRowGroup(30);
pivotTable = spreadsheet.getRange('A1').createPivotTable(sourceData);
pivotValue = pivotTable.addPivotValue(32, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
pivotGroup = pivotTable.addRowGroup(11);
pivotGroup.sortDescending()
.sortBy(pivotTable.getPivotValues()[0], []);
pivotGroup = pivotTable.addRowGroup(27);
pivotGroup.sortDescending()
.sortBy(pivotTable.getPivotValues()[0], []);
pivotTable = spreadsheet.getRange('A1').createPivotTable(sourceData);
pivotValue = pivotTable.addPivotValue(32, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
pivotValue = pivotTable.addPivotValue(30, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
pivotGroup = pivotTable.addRowGroup(11);
pivotGroup.sortDescending()
.sortBy(pivotTable.getPivotValues()[0], []);
pivotGroup = pivotTable.addRowGroup(27);
pivotGroup.sortDescending()
.sortBy(pivotTable.getPivotValues()[0], []);
pivotTable = spreadsheet.getRange('A1').createPivotTable(sourceData);
pivotValue = pivotTable.addPivotValue(32, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
pivotValue = pivotTable.addPivotValue(30, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
pivotValue = pivotTable.addPivotValue(31, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
pivotGroup = pivotTable.addRowGroup(11);
pivotGroup.sortDescending()
.sortBy(pivotTable.getPivotValues()[0], []);
pivotGroup = pivotTable.addRowGroup(27);
pivotGroup.sortDescending()
.sortBy(pivotTable.getPivotValues()[0], []);
pivotTable = spreadsheet.getRange('A1').createPivotTable(sourceData);
pivotValue = pivotTable.addPivotValue(32, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
pivotValue = pivotTable.addPivotValue(30, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
pivotValue = pivotTable.addPivotValue(31, SpreadsheetApp.PivotTableSummarizeFunction.AVERAGE);
pivotGroup = pivotTable.addRowGroup(11);
pivotGroup.sortDescending()
.sortBy(pivotTable.getPivotValues()[0], []);
pivotGroup = pivotTable.addRowGroup(27);
pivotGroup.sortDescending()
.sortBy(pivotTable.getPivotValues()[0], []);
spreadsheet.getActiveSheet().setColumnWidth(1, 282);
spreadsheet.getActiveSheet().setColumnWidth(1, 222);
spreadsheet.getActiveSheet().setColumnWidth(2, 301);
spreadsheet.getRange('D:D').activate();
spreadsheet.getActiveRangeList().setNumberFormat('#,##0.00')
.setNumberFormat('#,##0.0');
spreadsheet.getRange('C:C').activate();
spreadsheet.getActiveRangeList().setNumberFormat('#,##0.00')
.setNumberFormat('#,##0.0')
.setNumberFormat('#,##0');
spreadsheet.getRange('E:E').activate();
spreadsheet.getActiveRangeList().setNumberFormat('#,##0.00')
.setNumberFormat('#,##0.0')
.setNumberFormat('#,##0');
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Зведена по Клієнтам'), true);
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Зведена по Клієнтам/Товарам'), true);
spreadsheet.duplicateActiveSheet();
spreadsheet.getActiveSheet().setName('Зведена по Товарам');
spreadsheet.getRange('B5').activate();
pivotTable = spreadsheet.getRange('A1').createPivotTable(sourceData);
pivotValue = pivotTable.addPivotValue(32, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
pivotValue = pivotTable.addPivotValue(30, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
pivotValue = pivotTable.addPivotValue(31, SpreadsheetApp.PivotTableSummarizeFunction.AVERAGE);
pivotGroup = pivotTable.addRowGroup(27);
pivotGroup.sortDescending()
.sortBy(pivotTable.getPivotValues()[0], []);
pivotTable = spreadsheet.getRange('A1').createPivotTable(sourceData);
pivotValue = pivotTable.addPivotValue(32, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
pivotValue = pivotTable.addPivotValue(30, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
pivotValue = pivotTable.addPivotValue(31, SpreadsheetApp.PivotTableSummarizeFunction.AVERAGE);
pivotValue = pivotTable.addPivotValue(5, SpreadsheetApp.PivotTableSummarizeFunction.COUNTA);
pivotGroup = pivotTable.addRowGroup(27);
pivotGroup.sortDescending()
.sortBy(pivotTable.getPivotValues()[0], []);
pivotTable = spreadsheet.getRange('A1').createPivotTable(sourceData);
pivotValue = pivotTable.addPivotValue(32, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
pivotValue = pivotTable.addPivotValue(30, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
pivotValue = pivotTable.addPivotValue(31, SpreadsheetApp.PivotTableSummarizeFunction.AVERAGE);
pivotValue = pivotTable.addPivotValue(5, SpreadsheetApp.PivotTableSummarizeFunction.COUNTUNIQUE);
pivotGroup = pivotTable.addRowGroup(27);
pivotGroup.sortDescending()
.sortBy(pivotTable.getPivotValues()[0], []);
spreadsheet.getActiveSheet().setColumnWidth(2, 169);
spreadsheet.getActiveSheet().setColumnWidth(1, 492);
spreadsheet.getActiveSheet().setColumnWidth(3, 67);
spreadsheet.getActiveSheet().setColumnWidth(4, 82);
pivotTable = spreadsheet.getRange('A1').createPivotTable(sourceData);
pivotValue = pivotTable.addPivotValue(32, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
pivotValue = pivotTable.addPivotValue(30, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
pivotValue = pivotTable.addPivotValue(5, SpreadsheetApp.PivotTableSummarizeFunction.COUNTUNIQUE);
pivotValue = pivotTable.addPivotValue(31, SpreadsheetApp.PivotTableSummarizeFunction.AVERAGE);
pivotGroup = pivotTable.addRowGroup(27);
pivotGroup.sortDescending()
.sortBy(pivotTable.getPivotValues()[0], []);
pivotTable = spreadsheet.getRange('A1').createPivotTable(sourceData);
pivotValue = pivotTable.addPivotValue(5, SpreadsheetApp.PivotTableSummarizeFunction.COUNTUNIQUE);
pivotValue = pivotTable.addPivotValue(32, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
pivotValue = pivotTable.addPivotValue(30, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
pivotValue = pivotTable.addPivotValue(31, SpreadsheetApp.PivotTableSummarizeFunction.AVERAGE);
pivotGroup = pivotTable.addRowGroup(27);
pivotGroup.sortDescending()
.sortBy(pivotTable.getPivotValues()[1], []);
spreadsheet.getActiveSheet().setColumnWidth(2, 78);

Proposals 5

Oleg M.

109 0

Projects 105
Rating -
Rating 1 115

Budget: 1000 UAH Deadline: 1 day

Good day .
Great experience in developing script for Google tables.
Write in ls, you need to see the script and tables.

Dmitro Medvid

16 0

Projects 16
Rating -
Rating 621

Budget: 1000 UAH Deadline: 4 days

Good day I have experience in creating appScript. Write to PF with pleasure I will help you.

The list does not show proposals concealed by the client or freelancer with a Plus profile, as well as proposals violating rules

Current freelance projects in the category Desktop Apps

Refinement of the Python program for the new Twitter (X) chat interface.

Python 52 proposals 31 July

Not specified
Refinement of the CRM in 1C: displaying the client's bonus balance

Testing & QA 11 proposals 29 July

Not specified
Setting up multiple Viber accounts on one remote desktop

13 proposals 28 July

Not specified
Light application

PHP 63 proposals 28 July

171 USD
App : Digital Marketplace for Skilled Trades in Morocco

Web Programming 66 proposals 22 July

500 USD

Oleksandr Churnik
Kyiv, Ukraine

Projects 22
Rating -
Rating 493

Oleg M.

Dmitro Medvid

Proposals are currently absent

Current freelance projects in the category Desktop Apps

Refinement of the Python program for the new Twitter (X) chat interface.

Refinement of the CRM in 1C: displaying the client's bonus balance

Setting up multiple Viber accounts on one remote desktop

Light application

App : Digital Marketplace for Skilled Trades in Morocco