0
146
VistasWhat Do I Need to Change in My Script to Add Column A to Column B Returning the Answer in Column B Each Time I Hit My Button to Add Them?
I'm brand new to java & scripting in Google Sheets & this is my first question on here, so any etiquette tips are appreciated.
I have been learning so much from this site, but I've been stuck on this simple error for weeks now & finally just have to ask since I can't find a solution anywhere.
EDIT to Hopefully be More Clear
I have a sheet with pricing in column "U" with formulas in columns "V" & "W" that change according to the value in "U". I created a button & attached a script that I would like for it to add the value of each cell in V to the value in W of the same row, for one iteration each time I select the button, & returning the new values in the same cell of column W.
I've tried a lot of script variations that all returned different results, but none just simply adding these two columns together. Below is what I've patched together that almost does exactly what I need, but it needs a tweak somewhere & I can't figure it out.
function testPlus10(){
var colV = SpreadsheetApp.getActiveSheet().getRange("V2:V100").getValue();
var cell = SpreadsheetApp.getActiveSheet().getRange("W2:W100");
var cellValues = cell.getValues().map(function(row) {return [row[0] + colV]});
cell.setValues(cellValues);
}
Instead of adding the value in (From the example above) V2 to W2, V3 to W3, & so on down the column, it only adds the value of V2 to every cell in W. Meaning that from the example: my current script returns values of V2+W2 in W2, V2+W3 in W3, V2+W4 in W4, instead of V2+W2 in W2, V3+W3 in W3, V4+W4 in W4, ect... It adds a $1 to every cell in W.
Hopefully this makes more sense.
1 Respuestas
Responde la pregunta0
Assuming this is the expected behavior, you can use this code, which is pretty simple and can be used for multiple columns of your choice.
function sumColumns(){
var spreadsheet = SpreadsheetApp.getActive();
var currentRow = spreadsheet.getDataRange().getLastRow(); //Get the last row with value on your sheet data as a whole to only scan rows with values
for(var x =2; x<=currentRow; x++){ //Loop starts at row 2
if(spreadsheet.getRange("V"+x).getValue() == ""){ //Checks if V (row# or x) value is null
Logger.log("Cell V"+x+" is empty"); //Logs the result for review
}
else{
var res = spreadsheet.getRange("V"+x).getValue() + spreadsheet.getRange("W"+x).getValue(); //SUM of V & W values
spreadsheet.getRange("W"+x).setValue(res); //Replace W value with "res"
}
}
}