Extract and concat values matching ID from array google script
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
sorry for posting this which is probably super-easy but I'm pretty new to Google scripting and I really can't get through this in any way.
I have a table which contains form entries of clients who rent products, and it looks like:
Col A:Timestamps --------- Col B:Client------------ColC:Quantity--------- Col D:Code
Timestamps are used as ID; same client can rent multiple products, so in this case timestamps and name are repeated while quantities and codes vary.
Screenshot of my sheet
Starting from a known ID,
I would need to:
1 - write inside a new temporary range values (client/quant./products) which matches with my ID;
2 - if there are multiple entries matching with given ID I would need to write inside a cell a unique string which contains Client name (col B) only once + all matching quantities and products (col C, col D).
I think I'm getting confused by Array dimensions and so on, but I really can figure out how to do this!
Here the code:
function compare() {
var sheet = SpreadsheetApp.openById("IdSheet");
var ss = sheet.getSheetByName("SheetName");
var range1 = ss.getRange("A1:D"); // range of data from form - Timestamps (used as Id) | ClientName | Quantity | Code
var data1 = range1.getDisplayValues();
var id = ss.getRange("E3").getDisplayValue(); // Id to look for in column A (Timestamps)
// look for Id in A column
var count = ;
for(var i=0; i<data1.length-1; i++){
var timestamp = data1[i][0];
if(timestamp == id){
// count how many Ids have been duplicated
count.push(timestamp);
// Log of Client + Product who have same Id
Logger.log(data1[i][1]+','+data1[i][3]);
// First question: how can I write filtered values inside range F1:H?
};
};
// If Id is unique find row
if (count.length == 1 ) {
for(var j = 0; j < data1.length;j++){
if(data1[j][0] == id){
ss.getRange("E6").setValue(j+1);
};
};
}
else {
// Second question: if there are multiple entries with same Id I would need to write inside a cell
//a unique string which contains Client name (col B) of the first Id found + all matching products (col C, col D).
..............
}
}
Any help would be so much appreciated!
Thanks!
google-apps-script google-sheets
edited Nov 22 '18 at 15:41
TheMaster
10.7k3937
asked Nov 22 '18 at 15:27
SilviramaSilvirama
11
add a comment |
sorry for posting this which is probably super-easy but I'm pretty new to Google scripting and I really can't get through this in any way.
I have a table which contains form entries of clients who rent products, and it looks like:
Col A:Timestamps --------- Col B:Client------------ColC:Quantity--------- Col D:Code
Timestamps are used as ID; same client can rent multiple products, so in this case timestamps and name are repeated while quantities and codes vary.
Screenshot of my sheet
Starting from a known ID,
I would need to:
1 - write inside a new temporary range values (client/quant./products) which matches with my ID;
2 - if there are multiple entries matching with given ID I would need to write inside a cell a unique string which contains Client name (col B) only once + all matching quantities and products (col C, col D).
I think I'm getting confused by Array dimensions and so on, but I really can figure out how to do this!
Here the code:
function compare() {
var sheet = SpreadsheetApp.openById("IdSheet");
var ss = sheet.getSheetByName("SheetName");
var range1 = ss.getRange("A1:D"); // range of data from form - Timestamps (used as Id) | ClientName | Quantity | Code
var data1 = range1.getDisplayValues();
var id = ss.getRange("E3").getDisplayValue(); // Id to look for in column A (Timestamps)
// look for Id in A column
var count = ;
for(var i=0; i<data1.length-1; i++){
var timestamp = data1[i][0];
if(timestamp == id){
// count how many Ids have been duplicated
count.push(timestamp);
// Log of Client + Product who have same Id
Logger.log(data1[i][1]+','+data1[i][3]);
// First question: how can I write filtered values inside range F1:H?
};
};
// If Id is unique find row
if (count.length == 1 ) {
for(var j = 0; j < data1.length;j++){
if(data1[j][0] == id){
ss.getRange("E6").setValue(j+1);
};
};
}
else {
// Second question: if there are multiple entries with same Id I would need to write inside a cell
//a unique string which contains Client name (col B) of the first Id found + all matching products (col C, col D).
..............
}
}
Any help would be so much appreciated!
Thanks!
google-apps-script google-sheets
edited Nov 22 '18 at 15:41
TheMaster
10.7k3937
asked Nov 22 '18 at 15:27
SilviramaSilvirama
11
Your code refers to two questions.1 = how can I write filtered values inside range F1:H? What does this mean? Please give an example. 2 = if there are multiple entries with same Id I would need to write inside a cell a unique string which contains Client name (col B) of the first Id found + all matching products (col C, col D). Please give an example of what this would look like and where you would put it; and why ignore the second ID found?
– Tedinoz
Dec 11 '18 at 2:06
add a comment |
sorry for posting this which is probably super-easy but I'm pretty new to Google scripting and I really can't get through this in any way.
I have a table which contains form entries of clients who rent products, and it looks like:
Col A:Timestamps --------- Col B:Client------------ColC:Quantity--------- Col D:Code
Timestamps are used as ID; same client can rent multiple products, so in this case timestamps and name are repeated while quantities and codes vary.
Screenshot of my sheet
Starting from a known ID,
I would need to:
1 - write inside a new temporary range values (client/quant./products) which matches with my ID;
2 - if there are multiple entries matching with given ID I would need to write inside a cell a unique string which contains Client name (col B) only once + all matching quantities and products (col C, col D).
I think I'm getting confused by Array dimensions and so on, but I really can figure out how to do this!
Here the code:
function compare() {
var sheet = SpreadsheetApp.openById("IdSheet");
var ss = sheet.getSheetByName("SheetName");
var range1 = ss.getRange("A1:D"); // range of data from form - Timestamps (used as Id) | ClientName | Quantity | Code
var data1 = range1.getDisplayValues();
var id = ss.getRange("E3").getDisplayValue(); // Id to look for in column A (Timestamps)
// look for Id in A column
var count = ;
for(var i=0; i<data1.length-1; i++){
var timestamp = data1[i][0];
if(timestamp == id){
// count how many Ids have been duplicated
count.push(timestamp);
// Log of Client + Product who have same Id
Logger.log(data1[i][1]+','+data1[i][3]);
// First question: how can I write filtered values inside range F1:H?
};
};
// If Id is unique find row
if (count.length == 1 ) {
for(var j = 0; j < data1.length;j++){
if(data1[j][0] == id){
ss.getRange("E6").setValue(j+1);
};
};
}
else {
// Second question: if there are multiple entries with same Id I would need to write inside a cell
//a unique string which contains Client name (col B) of the first Id found + all matching products (col C, col D).
..............
}
}
Any help would be so much appreciated!
Thanks!
google-apps-script google-sheets
edited Nov 22 '18 at 15:41
TheMaster
10.7k3937
asked Nov 22 '18 at 15:27
SilviramaSilvirama
11
sorry for posting this which is probably super-easy but I'm pretty new to Google scripting and I really can't get through this in any way.
I have a table which contains form entries of clients who rent products, and it looks like:
Col A:Timestamps --------- Col B:Client------------ColC:Quantity--------- Col D:Code
Timestamps are used as ID; same client can rent multiple products, so in this case timestamps and name are repeated while quantities and codes vary.
Screenshot of my sheet
Starting from a known ID,
I would need to:
1 - write inside a new temporary range values (client/quant./products) which matches with my ID;
2 - if there are multiple entries matching with given ID I would need to write inside a cell a unique string which contains Client name (col B) only once + all matching quantities and products (col C, col D).
I think I'm getting confused by Array dimensions and so on, but I really can figure out how to do this!
Here the code:
function compare() {
var sheet = SpreadsheetApp.openById("IdSheet");
var ss = sheet.getSheetByName("SheetName");
var range1 = ss.getRange("A1:D"); // range of data from form - Timestamps (used as Id) | ClientName | Quantity | Code
var data1 = range1.getDisplayValues();
var id = ss.getRange("E3").getDisplayValue(); // Id to look for in column A (Timestamps)
// look for Id in A column
var count = ;
for(var i=0; i<data1.length-1; i++){
var timestamp = data1[i][0];
if(timestamp == id){
// count how many Ids have been duplicated
count.push(timestamp);
// Log of Client + Product who have same Id
Logger.log(data1[i][1]+','+data1[i][3]);
// First question: how can I write filtered values inside range F1:H?
};
};
// If Id is unique find row
if (count.length == 1 ) {
for(var j = 0; j < data1.length;j++){
if(data1[j][0] == id){
ss.getRange("E6").setValue(j+1);
};
};
}
else {
// Second question: if there are multiple entries with same Id I would need to write inside a cell
//a unique string which contains Client name (col B) of the first Id found + all matching products (col C, col D).
..............
}
}
Any help would be so much appreciated!
Thanks!
google-apps-script google-sheets
google-apps-script google-sheets
edited Nov 22 '18 at 15:41
TheMaster
10.7k3937
asked Nov 22 '18 at 15:27
SilviramaSilvirama
11
edited Nov 22 '18 at 15:41
TheMaster
10.7k3937
asked Nov 22 '18 at 15:27
SilviramaSilvirama
11
edited Nov 22 '18 at 15:41
TheMaster
10.7k3937
edited Nov 22 '18 at 15:41
TheMaster
10.7k3937
edited Nov 22 '18 at 15:41
TheMaster
10.7k3937
10.7k3937
asked Nov 22 '18 at 15:27
SilviramaSilvirama
11
asked Nov 22 '18 at 15:27
SilviramaSilvirama
11
asked Nov 22 '18 at 15:27
SilviramaSilvirama
11
11
Your code refers to two questions.1 = how can I write filtered values inside range F1:H? What does this mean? Please give an example. 2 = if there are multiple entries with same Id I would need to write inside a cell a unique string which contains Client name (col B) of the first Id found + all matching products (col C, col D). Please give an example of what this would look like and where you would put it; and why ignore the second ID found?
– Tedinoz
Dec 11 '18 at 2:06
add a comment |
Your code refers to two questions.1 = how can I write filtered values inside range F1:H? What does this mean? Please give an example. 2 = if there are multiple entries with same Id I would need to write inside a cell a unique string which contains Client name (col B) of the first Id found + all matching products (col C, col D). Please give an example of what this would look like and where you would put it; and why ignore the second ID found?
– Tedinoz
Dec 11 '18 at 2:06
Your code refers to two questions.1 = how can I write filtered values inside range F1:H? What does this mean? Please give an example. 2 = if there are multiple entries with same Id I would need to write inside a cell a unique string which contains Client name (col B) of the first Id found + all matching products (col C, col D). Please give an example of what this would look like and where you would put it; and why ignore the second ID found?
– Tedinoz
Dec 11 '18 at 2:06
Your code refers to two questions.1 = how can I write filtered values inside range F1:H? What does this mean? Please give an example. 2 = if there are multiple entries with same Id I would need to write inside a cell a unique string which contains Client name (col B) of the first Id found + all matching products (col C, col D). Please give an example of what this would look like and where you would put it; and why ignore the second ID found?
– Tedinoz
Dec 11 '18 at 2:06
add a 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
});
}
});
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%2f53434110%2fextract-and-concat-values-matching-id-from-array-google-script%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
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.
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%2f53434110%2fextract-and-concat-values-matching-id-from-array-google-script%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
Your code refers to two questions.1 = how can I write filtered values inside range F1:H? What does this mean? Please give an example. 2 = if there are multiple entries with same Id I would need to write inside a cell a unique string which contains Client name (col B) of the first Id found + all matching products (col C, col D). Please give an example of what this would look like and where you would put it; and why ignore the second ID found?
– Tedinoz
Dec 11 '18 at 2:06