Processing each Row with pivot data from other cell
==================================
UPDATE 11 December 2019 My Question is more about Macro Script
The GOAL (in illustration)
to change below raw sheet:
to more readable format:
Basically what i'm doing is split the campaign name with the separator and parse it. I don't have the problem if the function on only process single cell,for example:
on "Report" Sheet the CELL B2 , is taking data from "Data" B2 ONLY
i got problem when the return data require conditional operator that involve specific condition. So while processing cell B2, it require content from E2, D2, etc
=====================================
i'm taking data from Google Ads/Analytics API to Google Sheet on specific worksheet (i call it 'Raw Data'). Now i'm using pattern for the campaign, so i can easily split/break with separator in order for me to get specific data.
For Example:
With this, by using underscore as separator, i can split campaign name, into various data:
Campaign Objective: Sales
Campaign Title: TBMB
Network: SEM
Branch: All
Targeting: Keywords
..etc
Then i create new sheet called Called CReport which consist the same data from Raw Data sheet, but in much better visualization for marketing people.
Now, after searching on Google, i found the solution for self reference cell. The script goes like this:
function getSegment(data,index){
temp=data.split("_");
return temp[index-1];
}
function dataParse(input,dataSegment){
return Array.isArray(input) ? input.map(function(e){
return e.map(function(f){
if(f!=""){
return getSegment(f,dataSegment);
}
}
)}
) : "false usage";
}
So if i want to have a column with Network Name, i can place this formula on row 2 (because row 1 is for table header) something like this:
=ArrayFormula(dataParse('RAW DATA'!B2:B;2))
Now my question: This works for self-reference cell, means if the data taken from B2 in RAW DATA sheet, it will be the only data referenced to cell in Campaign Report sheet.
If the pointer is in B2 on CReport Sheet require data not only from B2 in RAW DATA but also D2 Cell.
What script i need to add in my function ? i'm expecting the chunk of code will something like this
function dataParse(input,dataSegment){
return Array.isArray(input) ? input.map(function(e){
return e.map(function(f){
if(f!=""){
segmentData=getSegment(f,dataSegment);
if(segmentData=="google"){
returnData=get reference from column D //<---
}else{
returnData=get reference from column E //<---
}
return returnData
}
}
)}
) : "false usage";
}
Hope its clear enough. Thanks in Advance !
I modified your function in this way:
// range (String): It will be used to get the info in a range
function dataParse(input,dataSegment, range){
var val = "";
return Array.isArray(input) ? input.map(function(e, index){
return e.map(function(f){
if(f!=""){
// If col D has value google then take info from col B
if(f === "google") val = getDesiredRangeValue("B", range, index);
// else take info from col E
else val = getDesiredRangeValue("E", range, index);
// Take segment as needed
return getSegment(val,dataSegment);
}
}
)}
) : "false usage";
}
In order to make it work, I inserted an extra argument to the function. Now you will need to pass as an string the range in A1 notation in your ArrayFormula, this is because the input argument only gives you the values in the cells, and with that extra argument it will be possible to obtain extra info. To make it work fine, always use the same range as the next example shows:
=ArrayFormula(dataParse('RAW DATA'!D2:D5, 2,"D2:D5"))
or
=ArrayFormula(dataParse('RAW DATA'!D2:D, 2,"D2:D"))
Notice I also added a new function called getDesiredRangeValue, which will take the values from the column you need, depending if one of the cells from Col D has the value google. This is how the function looks:
/*
// A1 (String): The col from where you will want the info
// range (String): It will be used to get the info in a range
// index (Integer): It gives the index number from the main array gotten in the input arg
*/
function getDesiredRangeValue(A1, range, index){
var rowNumbers = range.match(/\d+/g);
// It checks if the range will has and end or it will prolong without specifying and end row
if(rowNumbers.length > 1){
var rangeCol = ss.getRange(A1 + rowNumbers[0] + ":" + A1 + rowNumbers[1]).getValues();
} else {
var rangeCol = ss.getRange(A1 + rowNumbers[0] + ":" + A1).getValues();
}
// It returns the whole value from each cell in the specified col
return rangeCol[index][0];
}
Code
Now your whole code will look like this:
// Global var
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("RAW DATA");
function getSegment(data,index){
temp=data.split("_");
return temp[index-1];
}
/*
// A1 (String): The col from where you will want the info
// range (String): It will be used to get the info in a range
// index (Integer): It gives the index number from the main array gotten in the input arg
*/
function getDesiredRangeValue(A1, range, index){
var rowNumbers = range.match(/\d+/g);
// It checks if the range will has and end or it will prolong without specifying and end row
if(rowNumbers.length > 1){
var rangeCol = ss.getRange(A1 + rowNumbers[0] + ":" + A1 + rowNumbers[1]).getValues();
} else {
var rangeCol = ss.getRange(A1 + rowNumbers[0] + ":" + A1).getValues();
}
// It returns the whole value from each cell in the specified col
return rangeCol[index][0];
}
// range (String): It will be used to get the info in a range
function dataParse(input,dataSegment, range){
var val = "";
return Array.isArray(input) ? input.map(function(e, index){
return e.map(function(f){
if(f!=""){
// If col D has value google then take info from col B
if(f === "google") val = getDesiredRangeValue("B", range, index);
// else take info from col E
else val = getDesiredRangeValue("E", range, index);
// Take segment as needed
return getSegment(val,dataSegment);
}
}
)}
) : "false usage";
}
Docs
These are the docs I used to help you: