I would like to have a script that does the following step:
- The user selects a cell e.g. A5 (column A would be a unique ID column)
- Click a button / execute a macro shortcut, and a prompt appears asking for a position. Ex.: user enters
A2
- Take the selected row and move it to the entered position
- Rename every column A value with the new value, reorder them and then move the row Ex.: The A5 value becomes the A2 value and position A5 row to A2, A2 value become the A3 value and move A2 row to A3 row, A3 value becomes A4 value and A3 row move to A4 etc...
Here's what I have so far:
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 sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var value = sheet.getActiveCell().getValue();
var range = sheet.getActiveRange().getA1Notation();
var result = ui.prompt(
'Move row',
'Which position do you want:',
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".
var selectValue = sheet.getRange(text).getValue();
var selectRange = sheet.getRange(text);
ui.alert('range: ' + selectRange + '.');
sheet.getRange(range).setValue(selectValue);
}
}
I'm at step 3, I'm trying to move the selected row to a another place with the moveTo()
, but it move every row under to the selected range and move it.
sheet.getRange("A1:E").moveTo(sheet.getRange("F1"));