Hi,
I have no code knowledge! I did the Google Sheets Macro save operation.
The transaction target has a macro record, you can check it from the macros area.
Transaction Target
"1" - Copy this on the page by the google function in Column B
"2" - Paste into the A and B Column fields on the page and copy the result.
"3" - Paste to A and B Column fields on the page, respectively, without formulas.
** Let the data be recorded correctly, for 3 seconds between each recording process.
We look forward to your support..
code
I have no code knowledge! I did the Google Sheets Macro save operation.
Link Table Macro
docs.google.com
The transaction target has a macro record, you can check it from the macros area.
Transaction Target
"1" - Copy this on the page by the google function in Column B
"2" - Paste into the A and B Column fields on the page and copy the result.
"3" - Paste to A and B Column fields on the page, respectively, without formulas.
** Let the data be recorded correctly, for 3 seconds between each recording process.
We look forward to your support..
code
VBA Code:
function myFunction() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('A2:B2').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('2'), true);
spreadsheet.getRange('A2').activate();
spreadsheet.getRange('1!A2:B2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
spreadsheet.getRange('A3').activate();
spreadsheet.getRange('A2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
spreadsheet.getRange('A2:A3').activate();
spreadsheet.getActiveRange().autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('A2:C2').activate();
var currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.UP).activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('3'), true);
spreadsheet.getRange('A1').activate();
spreadsheet.getRange('2!A2:C34').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
spreadsheet.getRange('A34').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('2'), true);
spreadsheet.getRange('A2:C2').activate();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.UP).activate();
spreadsheet.getRange('A2').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('1'), true);
spreadsheet.getRange('A3:B3').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('2'), true);
spreadsheet.getRange('1!A3:B3').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
spreadsheet.getRange('A3').activate();
spreadsheet.getRange('A2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
spreadsheet.getRange('A2:A3').activate();
spreadsheet.getActiveRange().autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('A2:C2').activate();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('3'), true);
spreadsheet.getRange('2!A2:C34').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
spreadsheet.getRange('A67').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('2'), true);
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.UP).activate();
spreadsheet.getRange('A2').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('1'), true);
spreadsheet.getRange('A4:B4').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('2'), true);
spreadsheet.getRange('1!A4:B4').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
spreadsheet.getRange('A3').activate();
spreadsheet.getRange('A2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
spreadsheet.getRange('A2:A3').activate();
spreadsheet.getActiveRange().autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('A2:C2').activate();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('3'), true);
spreadsheet.getRange('2!A2:C40').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
spreadsheet.getRange('A106').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('2'), true);
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.UP).activate();
spreadsheet.getRange('A2').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('1'), true);
spreadsheet.getRange('A5:B5').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('2'), true);
spreadsheet.getRange('1!A5:B5').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
spreadsheet.getRange('A3').activate();
spreadsheet.getRange('A2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
spreadsheet.getRange('A2:A3').activate();
spreadsheet.getActiveRange().autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
};
function myFunction1() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('A2:B2').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('2'), true);
spreadsheet.getRange('1!A2:B2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
spreadsheet.getRange('A3').activate();
spreadsheet.getRange('A2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
spreadsheet.getRange('A2:A3').activate();
spreadsheet.getActiveRange().autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('A2').activate();
var currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.NEXT).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('3'), true);
spreadsheet.getRange('2!A2:C34').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A2').activate();
spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
spreadsheet.getRange('A35').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('2'), true);
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
spreadsheet.getRange('A2').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('1'), true);
spreadsheet.getRange('A3:B3').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('2'), true);
spreadsheet.getRange('1!A3:B3').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
spreadsheet.getRange('A3').activate();
spreadsheet.getRange('A2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
spreadsheet.getRange('A2:A3').activate();
spreadsheet.getActiveRange().autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('A2').activate();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.NEXT).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('3'), true);
spreadsheet.getRange('2!A2:C34').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
spreadsheet.getRange('A68').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('2'), true);
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
spreadsheet.getRange('A2').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('1'), true);
spreadsheet.getRange('A4:B4').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('2'), true);
spreadsheet.getRange('A2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('1'), true);
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('2'), true);
spreadsheet.getRange('1!A4:B4').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
spreadsheet.getRange('A3').activate();
spreadsheet.getRange('A2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
spreadsheet.getRange('A2:A3').activate();
spreadsheet.getActiveRange().autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('A2').activate();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.NEXT).activate();
currentCell.activateAsCurrentCell();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('3'), true);
spreadsheet.getRange('2!A2:C40').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
spreadsheet.getRange('A107').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('2'), true);
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.UP).activate();
spreadsheet.getRange('A2').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('1'), true);
spreadsheet.getRange('A5').activate();
};
function myFunction2() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('A2:B2').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('2'), true);
spreadsheet.getRange('A2').activate();
spreadsheet.getRange('1!A2:B2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
spreadsheet.getRange('A3').activate();
spreadsheet.getRange('A2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
spreadsheet.getRange('A2:A3').activate();
spreadsheet.getActiveRange().autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('A2:C2').activate();
var currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('3'), true);
spreadsheet.getRange('A2').activate();
spreadsheet.getRange('2!A2:C34').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A4').activate();
spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
spreadsheet.getRange('A35').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('2'), true);
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
spreadsheet.getRange('A2').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('1'), true);
spreadsheet.getRange('A3:B3').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('2'), true);
spreadsheet.getRange('1!A3:B3').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
spreadsheet.getRange('A3').activate();
spreadsheet.getRange('A2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
spreadsheet.getRange('A2:A3').activate();
spreadsheet.getActiveRange().autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('A2:C2').activate();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('3'), true);
spreadsheet.getRange('2!A2:C34').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
spreadsheet.getRange('A68').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('2'), true);
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
spreadsheet.getRange('A2').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('1'), true);
spreadsheet.getRange('A4:B4').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('2'), true);
spreadsheet.getRange('1!A4:B4').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
spreadsheet.getRange('A3').activate();
spreadsheet.getRange('A2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
spreadsheet.getRange('A2:A3').activate();
spreadsheet.getActiveRange().autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('A2:C2').activate();
currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('3'), true);
spreadsheet.getRange('2!A2:C40').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
spreadsheet.getRange('A69').activate();
spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
spreadsheet.getRange('A107').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('2'), true);
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
spreadsheet.getRange('A2').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('1'), true);
spreadsheet.getRange('A5').activate();
};