It worked great. I'm going to use it in a timed trigger daily. I'm pretty new to this I'm sure I need to add an "else" statement in case there are no items checked at the time the function is ran(otherwise I'll get error code). I've tried adding it to your function but I'm not sure where and how the else function should be placed. Here is my code: function MoveLube(){ const sh = SpreadsheetApp.getActive(); const ss = sh.getSheetByName("Lube"); const outSheet = sh.getSheetByName("Lube_Hist"); let data = ss.getRange(11,1,ss.getLastRow()-1,17).getValues(); let out = []; for (let i = 0; i < data.length; i++){ if(data[i][16] == true){; out.push(data[i]); ss.deleteRow(i+11); data.splice(i,1); i--; } } outSheet.getRange(outSheet.getLastRow()+1,1,out.length,17).setValues(out); }
What should i be learning prior to this ? I am lost but am eager to learn google sheets to streamline tasks within my company. Please help! THe videos I find are either extemely basic OR far beyond my coprehension.
There are two ways to approach learning to play Piano Man: 1 Don't learn actual musical notation or, just literally learn the notes for Piano Man 2 Start from the beginning and learn music theory, chord progression, "Mary Had a Little Lamb," until you can read the sheet music for Piano Man and learn it I try to balance my videos so that if someone just wants to do the TASK they can implement the code for it, but if they actually want to know why it does what it does I also try to go through that. So it depends: what all do you want to learn? The fullest starting place would be ANY coding course. Google Apps Script is based on Javascript, but by no means must one start there. The important thing to coding fundamentals are true regardless of language. But if you just want to learn how to do THIS on your sheet then you can do that, too. AND THERE'S NO SHAME IN THAT EITHER
Hi Spencer sad to hear no more videos for foreseeable. Having an issue with line 7 of this code. let out = []; when I log(out) the info reads blank? I’ve even used the same columns as your video to see if this fixes but no luck :/
Nevermind, good sleep and fresh head worked 👌 my data = was getting column c to e rather than a to c. Code was let data = ss.range(2,3,ss.getLastRow()-1,3).getValues(); Should have been for my case ss.range(2,1,ss.getLastRow()-1,3) If anyone needed this (:
Damn, im learning so much from you it's insane but im still struggling... I'm working on a sheet basically, a task sheet with archive the goal is to make it when column I ( has checkboxes ) gets "true" it creates date stamp in column K and the next action is to move row that has been marked by that check box to archive. I was able to make a date stamp but it won't move row. Could you enlighten me a bit :"3
It really worked in one of my sheet but when I tried it in another file, I get this error: "Exception: The number of rows in the range must be at least 1" This error is from outSheet.getRange(outSheet.getLastRow()+1,1,out.length,3).setValues(out); I don't know what's wrong. Any ideas?
Thank you for tbis video. You're such a great help. Just 1 question, my check box is in column 1, i want to move entire row but not included the check box to the destination.. How to do it? Thank you in advance.
This is one of the trickier ones to do that because it's storing then posting all the rows at once. But it should be to replace out.push(data[i]) with out.push(data[i].shift())
@@SpencerFarris Thank you for your reply. This my script function moveCheckedOB(){ const sh = SpreadsheetApp.getActive(); const ss = sh.getSheetByName("ENTRY"); const outSheet = sh.getSheetByName("OUTBOUND"); let data = ss.getRange(2,1,ss.getLastRow()-1,38).getValues(); let out = []; for (let i = 0; i < data.length; i++){ if (data[i][0] == true){ out.push(data[i]); ss.deleteRow(i+2); data.splice(i,1); i--; } } outSheet.getRange(outSheet.getLastRow()+1,1,out.length,38).setValues(out); } My check box is in Column A. I want to move entire row to the destination except Column A and the moved row will start in Column B in the destination. How can i do it? Your videos really help me a lot. Thanks
First of all your channel is awesome! And, would you be so kind and could you help me and check this bit for me? What if we have more columns in the spreadsheet (let's say 10), is my code correct? let data = sourceSheet.getRange(2,1,spreadSheet.getLastRow()-1,10).getValues(); let out = [ ]; for (let i = 0; i < data.length; i++){ if(data[i][10] == true){ out.push(data[i]); } }
your channel is really underrated. keep grinding man. it's very helpful.
Thank you!
thank you!
It worked great. I'm going to use it in a timed trigger daily. I'm pretty new to this I'm sure I need to add an "else" statement in case there are no items checked at the time the function is ran(otherwise I'll get error code). I've tried adding it to your function but I'm not sure where and how the else function should be placed. Here is my code:
function MoveLube(){
const sh = SpreadsheetApp.getActive();
const ss = sh.getSheetByName("Lube");
const outSheet = sh.getSheetByName("Lube_Hist");
let data = ss.getRange(11,1,ss.getLastRow()-1,17).getValues();
let out = [];
for (let i = 0; i < data.length; i++){
if(data[i][16] == true){;
out.push(data[i]);
ss.deleteRow(i+11);
data.splice(i,1);
i--;
}
}
outSheet.getRange(outSheet.getLastRow()+1,1,out.length,17).setValues(out);
}
What error are you getting?
Try adding
if (!out || !out.length) {
return
}
before the final line
What should i be learning prior to this ? I am lost but am eager to learn google sheets to streamline tasks within my company. Please help! THe videos I find are either extemely basic OR far beyond my coprehension.
There are two ways to approach learning to play Piano Man:
1 Don't learn actual musical notation or, just literally learn the notes for Piano Man
2 Start from the beginning and learn music theory, chord progression, "Mary Had a Little Lamb," until you can read the sheet music for Piano Man and learn it
I try to balance my videos so that if someone just wants to do the TASK they can implement the code for it, but if they actually want to know why it does what it does I also try to go through that.
So it depends: what all do you want to learn? The fullest starting place would be ANY coding course. Google Apps Script is based on Javascript, but by no means must one start there. The important thing to coding fundamentals are true regardless of language.
But if you just want to learn how to do THIS on your sheet then you can do that, too. AND THERE'S NO SHAME IN THAT EITHER
I have a specific question how I can contact you I contact you ?
Best!
Hi Spencer sad to hear no more videos for foreseeable. Having an issue with line 7 of this code. let out = []; when I log(out) the info reads blank? I’ve even used the same columns as your video to see if this fixes but no luck :/
Nevermind, good sleep and fresh head worked 👌 my data = was getting column c to e rather than a to c. Code was let data = ss.range(2,3,ss.getLastRow()-1,3).getValues();
Should have been for my case ss.range(2,1,ss.getLastRow()-1,3)
If anyone needed this (:
@@joshwilliams9481 Glad you got it!
Damn, im learning so much from you it's insane but im still struggling... I'm working on a sheet basically, a task sheet with archive the goal is to make it when column I ( has checkboxes ) gets "true" it creates date stamp in column K and the next action is to move row that has been marked by that check box to archive. I was able to make a date stamp but it won't move row. Could you enlighten me a bit :"3
What's your code?
It really worked in one of my sheet but when I tried it in another file, I get this error:
"Exception: The number of rows in the range must be at least 1"
This error is from outSheet.getRange(outSheet.getLastRow()+1,1,out.length,3).setValues(out);
I don't know what's wrong. Any ideas?
Are there any values in the outSheet prior to running the script?
@Elizabeth Trinh Put a header row on your outSheet and see if that fixes it
Thank you for tbis video. You're such a great help. Just 1 question, my check box is in column 1, i want to move entire row but not included the check box to the destination.. How to do it? Thank you in advance.
This is one of the trickier ones to do that because it's storing then posting all the rows at once. But it should be to replace out.push(data[i]) with out.push(data[i].shift())
@@SpencerFarris Thank you for your reply. This my script
function moveCheckedOB(){
const sh = SpreadsheetApp.getActive();
const ss = sh.getSheetByName("ENTRY");
const outSheet = sh.getSheetByName("OUTBOUND");
let data = ss.getRange(2,1,ss.getLastRow()-1,38).getValues();
let out = [];
for (let i = 0; i < data.length; i++){
if (data[i][0] == true){
out.push(data[i]);
ss.deleteRow(i+2);
data.splice(i,1);
i--;
}
}
outSheet.getRange(outSheet.getLastRow()+1,1,out.length,38).setValues(out);
}
My check box is in Column A. I want to move entire row to the destination except Column A and the moved row will start in Column B in the destination. How can i do it? Your videos really help me a lot. Thanks
Would there be a way to pull from multiple sheets and organize them into one?
const ss = sh.getSheetByName ("sheet 1","sheet 2" so on and so on.
@@thedesignbuildexperience6219 To do what, exactly? Something to do with this video, or something completely different?
Sir please, How to move from master sheet or from query function
I apologize, but I don't understand what you're asking
@@SpencerFarris can we edit in importrange data
First of all your channel is awesome! And, would you be so kind and could you help me and check this bit for me? What if we have more columns in the spreadsheet (let's say 10), is my code correct?
let data = sourceSheet.getRange(2,1,spreadSheet.getLastRow()-1,10).getValues();
let out = [ ];
for (let i = 0; i < data.length; i++){
if(data[i][10] == true){
out.push(data[i]);
}
}
Sorry, silly me... if we have 10 columns I should have typed " if(data[i][9] == true){
:D
@@tomekgut5263 It looks correct from a cursory look. Is it working?
@@SpencerFarris hey! Thank you for replying! And yes! I counted incorrectly the columns in my sheet.