google sheetsgoogle-apps-script
I have a sheet containing all of my data.
What I want to do is to have a button to add the values in some cells in the current sheet to the first row with empty cells in the data sheet.
I want to do it in the sheets and not with the API (if it is possible to use the API in a sheet then that would work).
If the only way to add ui objects is by drawing then I know how to do that but the main problem is with the adding the row.
Yes, with data validation:
Eg Select ColumnB of Kids, Data (top right in image), Validation..., Cell range: Kids!B:B, Critera: List from a range Parents!A:A, select from options, Save.
This should fill the range with the grey down arrows and when clicking one of these a dropdown list such as that shown should appear.
If you were able to separate the mothers from the fathers (not divorce!) you should be able to apply a list of mothers only to one column and of fathers only to another, should you so wish.
With the following little piece of code you can accomplish that.
function myFind() {
var ss = SpreadsheetApp.getActive(), output = [];
var findData = ss.getSheetByName('fr').getDataRange().getValues();
var searchData = ss.getSheetByName('cql').getDataRange().getValues();
for(var i=1, iLen=findData.length; i<iLen; i++) {
for(var j=0, jLen=searchData.length; j<jLen; j++) {
for(var k=0, kLen=searchData[0].length; k<kLen; k++) {
var find = findData[i][0];
if(find == searchData[j][k]) {
output.push([find, "row "+(j+1)+"; "+"col "+(k+1)]);
}
}
}
}
return output;
}
Both data ranges are "captured" at once via the .getDataRange.getValues() method. The 2d array, that's being returned, includes empty rows and columns. This means that all we need to do is correct for the zero based array and we have a row and column index. Through the iterations (note the var i=1 to skip the header), the result is being pushed into an output array. Finally, the output is returned.
find
search
result
I've created an example file for you: get cell row and column index
Best Answer
To target the first empty row in the column, use the following code:
If you want to fill that row with data from an existing row, get the data from that row and set the values of
firstOpenRow:In order to run these blocks in-sheet, you will have to create a sidebar add-on which is relatively easy if you know basic HTML and CSS. Some example reference can be found here.
In that sidebar, you'll want to have an input field and button to fetch whatever dataset you are trying to target, and then another button to insert that data at the end of the sheet.
If you are looking to insert completely new data, use a form object in your sidebar to retrieve and send that data to your script. In the script, use the same principles as shown above but with the form data.