0
253
ViewsHow to get the all the values in multiple columns and store them in one variable
I'm trying to get the values of 4 columns (N, P, R, T) and store them in one column. Also, I thought of doing the same thing but for rows ( So like concatenate 4 cells in 4 columns and store them in a variable via a for loop). I think I would start doung that when I figure the columns first.
for example:-
so here i want to store the values of each column and store them in one variable and then sum all 4 values to get the value of all four columns which is (14+12+11+7) which is 44.
I wrote this code but it doesn't seem to me like its storing the values.
function percentage(){
const thisSheet = SpreadsheetApp.openByUrl(THIS).getSheetByName('WP_Data');
var range=thisSheet.getRangeList(['N3:N', 'P3:P', 'R3:R', 'T3:T']);
var rangeN= thisSheet.getRange("N3:N");
var valueN=rangeN.getValues();
var rangeP= thisSheet.getRange("P3:P");
var valueP=rangeP.getValues();
var rangeR= thisSheet.getRange("R3:R");
var valueR=rangeR.getValues();
var rangeT= thisSheet.getRange("T3:T");
var valueT=rangeT.getValues();
console.log()
var val =valueN + valueP + valueR + valueT;
}
would really appreciate the help.
If you need more info please let me know
·
Juan Pablo Isaza
2 answers
Answer question0
This might do the works for you
function percentage(){
const thisSheet = SpreadsheetApp.openByUrl(THIS).getSheetByName('WP_Data');
var range=ss.getRangeList(['N3:N', 'P3:P', 'R3:R', 'T3:T']);
var rangeN= ss.getRange("N3:N");
var valueN=rangeN.getValues();
var rangeP= ss.getRange("P3:P");
var valueP=rangeP.getValues();
var rangeR= ss.getRange("R3:R");
var valueR=rangeR.getValues();
var rangeT= ss.getRange("T3:T");
var valueT=rangeT.getValues();
//you can use concat or creat a new array to store these values.
const array = valueN.concat(valueP,valueR,valueT).flat();
//then use array.reduce function to sum all the items in the array.
const sum = array.reduce((value,sum) => value+sum)
// you will get the out put as 44
console.log(sum)
}
Is that what you asking for?
const string = array.toString();
console.log(string)
·
Juan Pablo Isaza
Report
0
Something like this?
function percentage(){
const thisSheet = SpreadsheetApp.openByUrl(THIS).getSheetByName('WP_Data');
var values = thisSheet.getRange(3,14,thisSheet.getLastRow()-2,7).getValues(); // N3:T??
var results = [];
var i = 0;
for( i=0; i<values.length; i++ ) {
results.push([values[i][0]+values[i][2]+values[i][4]+values[i][6]]); // index 0 = column N, etc.
}
// don't know what you want to do with it but you could use setValues(results)
thisSheet.getRange(??,??,results.length,1).setValues(results);
}
·
Juan Pablo Isaza
Report
Answer question