Function for moving data without slowing code
0
I have code so that when I press checkbox, the code will find the first empty row and put data there, see:
function onEdit(e) {
const INPUT_ROW_INDEX = 6;
if (INPUT_ROW_INDEX === 6) {
const range = e.range;
const spreadSheet = SpreadsheetApp.getActiveSpreadsheet(); `enter code here`
const rowIndex = range.getRow();
const columnIndex = range.getColumn();
const value = range.getValue();
if (value === true) {
const startColumnName = columnToLetter(columnIndex - 6);
const endColumnName = columnToLetter(columnIndex - 1);
const firstFreeColumnName = columnToLetter(columnIndex - 2);
const firstFreeRowIndex = getFirstEmptyRowByCell(firstFreeColumnName, INPUT_ROW_INDEX + 1, spreadSheet);
const copyRange = spreadSheet.getRange(startColumnName + rowIndex + ":" + endColumnName + rowIndex);
const deleteRange = spreadSheet.getRange(startColumnName + rowIndex + ":" + firstFreeColumnName + rowIndex);
copyRange.copyTo(spreadSheet.getRange(startColumnName + firstFreeRowIndex + ":" + endColumnName + firstFreeRowIndex), {
contentsOnly: true
});
deleteRange.clear();
range.setValue(false);
}
}
}
function columnToLetter(column){
var temp, letter = '';
while (column > 0) {
temp = (column - 1) % 26;
letter = String.fromCharCode(temp + 65) + letter;
column = (column - temp - 1) / 26;
}
return letter;
}
function getFirstEmptyRowByCell(firstFreeColumnName, startIndex, spreadSheet) {
var range = spreadSheet.getRange(
firstFreeColumnName + ':' + firstFreeColumnName);
for (var i = startIndex; i <= range.getLastRow(); i++){
if (!range.getCell(i, 1).getValue()) {
break;
}
}
return i;
}
My question is, if there exists some function or trick, so that when I press the checkbox data will move right under the checkbox row, and the row under the checkbox will be always empty, and I can repeat it how many times I want.
Thank for any advice.
Jan
javascript google-apps-script google-sheets
edited Nov 21 '18 at 9:20
Tedinoz
1,25921319
asked Nov 5 '18 at 10:22
Ján KolodzejJán Kolodzej
61
|
show 1 more comment
0
I have code so that when I press checkbox, the code will find the first empty row and put data there, see:
function onEdit(e) {
const INPUT_ROW_INDEX = 6;
if (INPUT_ROW_INDEX === 6) {
const range = e.range;
const spreadSheet = SpreadsheetApp.getActiveSpreadsheet(); `enter code here`
const rowIndex = range.getRow();
const columnIndex = range.getColumn();
const value = range.getValue();
if (value === true) {
const startColumnName = columnToLetter(columnIndex - 6);
const endColumnName = columnToLetter(columnIndex - 1);
const firstFreeColumnName = columnToLetter(columnIndex - 2);
const firstFreeRowIndex = getFirstEmptyRowByCell(firstFreeColumnName, INPUT_ROW_INDEX + 1, spreadSheet);
const copyRange = spreadSheet.getRange(startColumnName + rowIndex + ":" + endColumnName + rowIndex);
const deleteRange = spreadSheet.getRange(startColumnName + rowIndex + ":" + firstFreeColumnName + rowIndex);
copyRange.copyTo(spreadSheet.getRange(startColumnName + firstFreeRowIndex + ":" + endColumnName + firstFreeRowIndex), {
contentsOnly: true
});
deleteRange.clear();
range.setValue(false);
}
}
}
function columnToLetter(column){
var temp, letter = '';
while (column > 0) {
temp = (column - 1) % 26;
letter = String.fromCharCode(temp + 65) + letter;
column = (column - temp - 1) / 26;
}
return letter;
}
function getFirstEmptyRowByCell(firstFreeColumnName, startIndex, spreadSheet) {
var range = spreadSheet.getRange(
firstFreeColumnName + ':' + firstFreeColumnName);
for (var i = startIndex; i <= range.getLastRow(); i++){
if (!range.getCell(i, 1).getValue()) {
break;
}
}
return i;
}
My question is, if there exists some function or trick, so that when I press the checkbox data will move right under the checkbox row, and the row under the checkbox will be always empty, and I can repeat it how many times I want.
Thank for any advice.
Jan
javascript google-apps-script google-sheets
edited Nov 21 '18 at 9:20
Tedinoz
1,25921319
asked Nov 5 '18 at 10:22
Ján KolodzejJán Kolodzej
61
Unfortunately there is no trick or predefined functions for doing the same , u have to write your own logic for this
– Arunprasanth K V
Nov 5 '18 at 10:24
can we see your html ? what data you want to paste it to the first row ? first row in the entire page , or in a particular div , or in a table please provide more info
– Arunprasanth K V
Nov 5 '18 at 10:26
Unrelated, but I fixed the indentation in your JavaScript and it appears you've forgot a bracket{for the last for loop ingetFirstEmptyRowByCell. I didn't want to fix it in case the typo was something that caused validity for your question.
– Johan
Nov 5 '18 at 10:28
Whole logic is that this script is used for writing working hours, now it is working when I press checkbox data will move to the first empty cell which is in the end of the table. I want to move data to the start of the table that I can see data at first sight and not scrolling to the end of the table.
– Ján Kolodzej
Nov 5 '18 at 10:38
@ArunprasanthKV its date data, only first row its moving like its working now, see the code. I want to move data (one row) under the row where is checkbox. Whole logic is that my data will move under chceckbox row and after that there will be always free row for faster code working, and final step will be that whole data in table will move down in one row. Hope this is understandable.
– Ján Kolodzej
Nov 6 '18 at 11:38
|
show 1 more comment
0
0
0
I have code so that when I press checkbox, the code will find the first empty row and put data there, see:
function onEdit(e) {
const INPUT_ROW_INDEX = 6;
if (INPUT_ROW_INDEX === 6) {
const range = e.range;
const spreadSheet = SpreadsheetApp.getActiveSpreadsheet(); `enter code here`
const rowIndex = range.getRow();
const columnIndex = range.getColumn();
const value = range.getValue();
if (value === true) {
const startColumnName = columnToLetter(columnIndex - 6);
const endColumnName = columnToLetter(columnIndex - 1);
const firstFreeColumnName = columnToLetter(columnIndex - 2);
const firstFreeRowIndex = getFirstEmptyRowByCell(firstFreeColumnName, INPUT_ROW_INDEX + 1, spreadSheet);
const copyRange = spreadSheet.getRange(startColumnName + rowIndex + ":" + endColumnName + rowIndex);
const deleteRange = spreadSheet.getRange(startColumnName + rowIndex + ":" + firstFreeColumnName + rowIndex);
copyRange.copyTo(spreadSheet.getRange(startColumnName + firstFreeRowIndex + ":" + endColumnName + firstFreeRowIndex), {
contentsOnly: true
});
deleteRange.clear();
range.setValue(false);
}
}
}
function columnToLetter(column){
var temp, letter = '';
while (column > 0) {
temp = (column - 1) % 26;
letter = String.fromCharCode(temp + 65) + letter;
column = (column - temp - 1) / 26;
}
return letter;
}
function getFirstEmptyRowByCell(firstFreeColumnName, startIndex, spreadSheet) {
var range = spreadSheet.getRange(
firstFreeColumnName + ':' + firstFreeColumnName);
for (var i = startIndex; i <= range.getLastRow(); i++){
if (!range.getCell(i, 1).getValue()) {
break;
}
}
return i;
}
My question is, if there exists some function or trick, so that when I press the checkbox data will move right under the checkbox row, and the row under the checkbox will be always empty, and I can repeat it how many times I want.
Thank for any advice.
Jan
javascript google-apps-script google-sheets
edited Nov 21 '18 at 9:20
Tedinoz
1,25921319
asked Nov 5 '18 at 10:22
Ján KolodzejJán Kolodzej
61
I have code so that when I press checkbox, the code will find the first empty row and put data there, see:
function onEdit(e) {
const INPUT_ROW_INDEX = 6;
if (INPUT_ROW_INDEX === 6) {
const range = e.range;
const spreadSheet = SpreadsheetApp.getActiveSpreadsheet(); `enter code here`
const rowIndex = range.getRow();
const columnIndex = range.getColumn();
const value = range.getValue();
if (value === true) {
const startColumnName = columnToLetter(columnIndex - 6);
const endColumnName = columnToLetter(columnIndex - 1);
const firstFreeColumnName = columnToLetter(columnIndex - 2);
const firstFreeRowIndex = getFirstEmptyRowByCell(firstFreeColumnName, INPUT_ROW_INDEX + 1, spreadSheet);
const copyRange = spreadSheet.getRange(startColumnName + rowIndex + ":" + endColumnName + rowIndex);
const deleteRange = spreadSheet.getRange(startColumnName + rowIndex + ":" + firstFreeColumnName + rowIndex);
copyRange.copyTo(spreadSheet.getRange(startColumnName + firstFreeRowIndex + ":" + endColumnName + firstFreeRowIndex), {
contentsOnly: true
});
deleteRange.clear();
range.setValue(false);
}
}
}
function columnToLetter(column){
var temp, letter = '';
while (column > 0) {
temp = (column - 1) % 26;
letter = String.fromCharCode(temp + 65) + letter;
column = (column - temp - 1) / 26;
}
return letter;
}
function getFirstEmptyRowByCell(firstFreeColumnName, startIndex, spreadSheet) {
var range = spreadSheet.getRange(
firstFreeColumnName + ':' + firstFreeColumnName);
for (var i = startIndex; i <= range.getLastRow(); i++){
if (!range.getCell(i, 1).getValue()) {
break;
}
}
return i;
}
My question is, if there exists some function or trick, so that when I press the checkbox data will move right under the checkbox row, and the row under the checkbox will be always empty, and I can repeat it how many times I want.
Thank for any advice.
Jan
javascript google-apps-script google-sheets
javascript google-apps-script google-sheets
edited Nov 21 '18 at 9:20
Tedinoz
1,25921319
asked Nov 5 '18 at 10:22
Ján KolodzejJán Kolodzej
61
edited Nov 21 '18 at 9:20
Tedinoz
1,25921319
asked Nov 5 '18 at 10:22
Ján KolodzejJán Kolodzej
61
edited Nov 21 '18 at 9:20
Tedinoz
1,25921319
edited Nov 21 '18 at 9:20
Tedinoz
1,25921319
edited Nov 21 '18 at 9:20
Tedinoz
1,25921319
1,25921319
asked Nov 5 '18 at 10:22
Ján KolodzejJán Kolodzej
61
asked Nov 5 '18 at 10:22
Ján KolodzejJán Kolodzej
61
asked Nov 5 '18 at 10:22
Ján KolodzejJán Kolodzej
61
61
Unfortunately there is no trick or predefined functions for doing the same , u have to write your own logic for this
– Arunprasanth K V
Nov 5 '18 at 10:24
can we see your html ? what data you want to paste it to the first row ? first row in the entire page , or in a particular div , or in a table please provide more info
– Arunprasanth K V
Nov 5 '18 at 10:26
Unrelated, but I fixed the indentation in your JavaScript and it appears you've forgot a bracket{for the last for loop ingetFirstEmptyRowByCell. I didn't want to fix it in case the typo was something that caused validity for your question.
– Johan
Nov 5 '18 at 10:28
Whole logic is that this script is used for writing working hours, now it is working when I press checkbox data will move to the first empty cell which is in the end of the table. I want to move data to the start of the table that I can see data at first sight and not scrolling to the end of the table.
– Ján Kolodzej
Nov 5 '18 at 10:38
@ArunprasanthKV its date data, only first row its moving like its working now, see the code. I want to move data (one row) under the row where is checkbox. Whole logic is that my data will move under chceckbox row and after that there will be always free row for faster code working, and final step will be that whole data in table will move down in one row. Hope this is understandable.
– Ján Kolodzej
Nov 6 '18 at 11:38
|
show 1 more comment
Unfortunately there is no trick or predefined functions for doing the same , u have to write your own logic for this
– Arunprasanth K V
Nov 5 '18 at 10:24
can we see your html ? what data you want to paste it to the first row ? first row in the entire page , or in a particular div , or in a table please provide more info
– Arunprasanth K V
Nov 5 '18 at 10:26
Unrelated, but I fixed the indentation in your JavaScript and it appears you've forgot a bracket{for the last for loop ingetFirstEmptyRowByCell. I didn't want to fix it in case the typo was something that caused validity for your question.
– Johan
Nov 5 '18 at 10:28
Whole logic is that this script is used for writing working hours, now it is working when I press checkbox data will move to the first empty cell which is in the end of the table. I want to move data to the start of the table that I can see data at first sight and not scrolling to the end of the table.
– Ján Kolodzej
Nov 5 '18 at 10:38
@ArunprasanthKV its date data, only first row its moving like its working now, see the code. I want to move data (one row) under the row where is checkbox. Whole logic is that my data will move under chceckbox row and after that there will be always free row for faster code working, and final step will be that whole data in table will move down in one row. Hope this is understandable.
– Ján Kolodzej
Nov 6 '18 at 11:38
Unfortunately there is no trick or predefined functions for doing the same , u have to write your own logic for this
– Arunprasanth K V
Nov 5 '18 at 10:24
Unfortunately there is no trick or predefined functions for doing the same , u have to write your own logic for this
– Arunprasanth K V
Nov 5 '18 at 10:24
can we see your html ? what data you want to paste it to the first row ? first row in the entire page , or in a particular div , or in a table please provide more info
– Arunprasanth K V
Nov 5 '18 at 10:26
can we see your html ? what data you want to paste it to the first row ? first row in the entire page , or in a particular div , or in a table please provide more info
– Arunprasanth K V
Nov 5 '18 at 10:26
Unrelated, but I fixed the indentation in your JavaScript and it appears you've forgot a bracket
{ for the last for loop in getFirstEmptyRowByCell. I didn't want to fix it in case the typo was something that caused validity for your question.– Johan
Nov 5 '18 at 10:28
Unrelated, but I fixed the indentation in your JavaScript and it appears you've forgot a bracket
{ for the last for loop in getFirstEmptyRowByCell. I didn't want to fix it in case the typo was something that caused validity for your question.– Johan
Nov 5 '18 at 10:28
Whole logic is that this script is used for writing working hours, now it is working when I press checkbox data will move to the first empty cell which is in the end of the table. I want to move data to the start of the table that I can see data at first sight and not scrolling to the end of the table.
– Ján Kolodzej
Nov 5 '18 at 10:38
Whole logic is that this script is used for writing working hours, now it is working when I press checkbox data will move to the first empty cell which is in the end of the table. I want to move data to the start of the table that I can see data at first sight and not scrolling to the end of the table.
– Ján Kolodzej
Nov 5 '18 at 10:38
@ArunprasanthKV its date data, only first row its moving like its working now, see the code. I want to move data (one row) under the row where is checkbox. Whole logic is that my data will move under chceckbox row and after that there will be always free row for faster code working, and final step will be that whole data in table will move down in one row. Hope this is understandable.
– Ján Kolodzej
Nov 6 '18 at 11:38
@ArunprasanthKV its date data, only first row its moving like its working now, see the code. I want to move data (one row) under the row where is checkbox. Whole logic is that my data will move under chceckbox row and after that there will be always free row for faster code working, and final step will be that whole data in table will move down in one row. Hope this is understandable.
– Ján Kolodzej
Nov 6 '18 at 11:38
|
show 1 more comment
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53152418%2ffunction-for-moving-data-without-slowing-code%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53152418%2ffunction-for-moving-data-without-slowing-code%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Unfortunately there is no trick or predefined functions for doing the same , u have to write your own logic for this
– Arunprasanth K V
Nov 5 '18 at 10:24
can we see your html ? what data you want to paste it to the first row ? first row in the entire page , or in a particular div , or in a table please provide more info
– Arunprasanth K V
Nov 5 '18 at 10:26
Unrelated, but I fixed the indentation in your JavaScript and it appears you've forgot a bracket
{for the last for loop ingetFirstEmptyRowByCell. I didn't want to fix it in case the typo was something that caused validity for your question.– Johan
Nov 5 '18 at 10:28
Whole logic is that this script is used for writing working hours, now it is working when I press checkbox data will move to the first empty cell which is in the end of the table. I want to move data to the start of the table that I can see data at first sight and not scrolling to the end of the table.
– Ján Kolodzej
Nov 5 '18 at 10:38
@ArunprasanthKV its date data, only first row its moving like its working now, see the code. I want to move data (one row) under the row where is checkbox. Whole logic is that my data will move under chceckbox row and after that there will be always free row for faster code working, and final step will be that whole data in table will move down in one row. Hope this is understandable.
– Ján Kolodzej
Nov 6 '18 at 11:38