Script, Macro for Google Sheets Access in All Books
The task is to make possible the launch of this script in all the tables.
Переписати скрипт який був записаний як макрос і що вказано нижче,
щоб він був виконаний для кожної таблиці в певній папці на 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);
-
1 day22 USD
1115 109 0 1 day22 USD
Good day .
Great experience in developing script for Google tables.
Write in ls, you need to see the script and tables.
-
4 days22 USD
652 16 0 4 days22 USD
Good day I have experience in creating appScript. Write to PF with pleasure I will help you.
Current freelance projects in the category Desktop Apps
Modification in the emulator's operation
45 USD
Hello. I downloaded the Gaminator CF Final slot machine emulator from the Internet. I really liked it, but there are some issues. There is an admin panel, but it opens freely and cannot be closed at all. It only closes along with the application. Also, the data does not save… Desktop Apps ∙ 2 days 12 hours back ∙ 10 proposals |
Development of custom software (Android application + CMS) for SUNMI K2 self-service kiosks (Fast food)
22 USD
We are looking for an experienced team or a Middle/Senior Android developer (possibly with Full-stack skills) to create proprietary software for self-service kiosks for a fast food retail chain. Currently, our terminals operate on a ready-made cloud integrator, but we are… Content Management Systems, Desktop Apps ∙ 7 days 7 hours back ∙ 23 proposals |
It is necessary to migrate the current working program from FoxPro to C#.Hello everyone! We have a program in FoxPro (it has several modules, it's an accounting program), we have the source code of the program, and if necessary, we can consult with people who support the current program. What needs to be done: 1. Analyze how everything works,… C#, Desktop Apps ∙ 12 days 15 hours back ∙ 19 proposals |
Kyiv