在Google Data Studio中合并列数据 [英] Combining Column Data in Google Data Studio
问题描述
我对GDS还是陌生的，到目前为止，我一直很喜欢.我正在尝试做的是创建一个Google表格，其中包含特定工作的需求和数量，然后使用Data Studio能够按需求进行搜索并将数量显示在同一字段中.
我已经模拟了一些我目前拥有的东西的例子:
我知道我可以通过为每种材料使用N个数量的列来完成类似的事情，并且包含该数量但对于我的应用而言不实际.
谢谢您的时间.
Data Studio无法对Material进行过滤，因为它无法识别唯一的组件.
我建议您在电子表格中创建第二个表，该表由脚本/宏创建，并按作业列出物料和数量.该工作表是Data Studio的来源，因此它将启用按Job或Material进行过滤.可以通过运行宏随时更新该表.
该表可能如下所示:
data Studio中的输出如下所示:
这是 Data Studio示例.
如果使用此Excel，则VBA会很容易编写，但是Google Scripts使用Javascript，因此为提供新的电子表格提供的代码示例肯定会花我一点时间(尽管其他人会更熟练).
Google脚本逻辑为:
对于每个工作
- 计算需求中的逗号数.需求数量= CommaCount加1.
- 解析需求以获取数量和材料详细信息
- 将第一个数量/材料复制到C&列中D担任这份工作.
- 对于每个其他要求，请复制作业"行，然后将其他数量/材料复制到相应的列C&中.D.
在Data Studio中
- 插入表格；维度:工作，需求；排序:升序.
- 插入过滤器；维度:工作；排序:升序.
- 插入过滤器；尺寸:材料；排序:升序.
更新该代码实现了以上概述的目标-拥有为GDS服务的第二张工作表.它获取原始作业信息并构建一张数据表，以标识每个作业的物料和数量的每种组合.该代码可能没有效率那么高，但是在此阶段更重要的是-它可以工作.
三个假设:1)每个作业始终使用一种以上的材料；2)物料代码不包含空格；3)数量始终是整数.
快速摘要
可以将一个主"功能分配给GDS_data工作表上的一个按钮.这使得重建GDS数据极为容易.这些功能是:
- BuildGDSData-主要功能
- CountJobs-好吧，...，工作数量
- CountMaterials-计算每个作业的需求数量
- CreateJobRows-插入新行，以便每个作业每个行有一行要求
- BuildMaterials-解析工作要求，并填充作业每一行的材料"和数量"列.
新的数据工作室页面
//转换GDS的职位信息函数BuildGDSData(){var ss = SpreadsheetApp.getActiveSpreadsheet();var source = ss.getSheetByName('jobinfo');//假设基本工作信息在名为"jobinfo"的工作表上var target = ss.getSheetByName('gds_data');//假定GDS数据建立在名为"gds_data"的工作表上//从头开始gds_data.删除所有内容target.clearContents();target.clearFormats();//从sheet = jobinfo获取数据范围var rangeToCopy = source.getRange(1，1，source.getMaxRows()，source.getMaxColumns());//将数据粘贴到cheet = gds_datarangeToCopy.copyTo(target.getRange(1，1));//在sheet = gds_data上为材料和数量添加标题target.getRange('C1').setValue('Material');target.getRange('D1').setValue('Qty');//将光标移动到工作表= gds_data上的单元格B2上-这是作业信息的开始var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();var range = sheet.getRange('B2:B2');sheet.setActiveRange(range);//调用CountJobs函数；将作业编号分配给变量"job_count"var job_count = CountJobs();//执行循环以获取作业数for(var i = 0; i< job_count; i ++){//调用函数CountMaterials;将作业的物料数量分配给变量MatCountvar MatCount = CountMaterials();//复制并插入作业的新行，以便每个需求有一行.将作业详细信息复制到每个新行//为此作业创建的行数=材质数减一.一个"是已经存在的作业信息行.CreateJobRows(((MatCount-1));//解析需求并将结果复制到Material和Qty列BuildMaterials();}}//计算并返回Jobs的数量=数据的行数减去一.一个"是标题行，函数CountJobs(){var numRows = SpreadsheetApp.getActiveSpreadsheet().getLastRow();var numCols = SpreadsheetApp.getActiveSpreadsheet().getLastColumn();返回(numRows-1);}//计算并返回当前作业的物料数量；数量=逗号数加1函数CountMaterials(){var activeSheet = SpreadsheetApp.getActiveSheet();var selection = activeSheet.getSelection();//获取此工作的需求值var text = selection.getCurrentCell().getValue();//使用split命令分解需求.以逗号分隔.var textArray = text.split(，");//计算拆分创建的组件数var Num_Materials = textArray.length;//返回组件数返回Num_Materials;}//创建新行以满足工作中的每个要求.count变量标识要创建的行数函数CreateJobRows(count){var sheet = SpreadsheetApp.getActiveSheet();for(var i = 0; i< count; i ++){//获取当前行号行= sheet.getActiveCell().getRow();//在当前行之后插入新行sheet.insertRowAfter(row);//将内容从当前行的单元格A和B复制到新行var rangeToCopy = sheet.getRange(row，1，1，2);rangeToCopy.copyTo(sheet.getRange(row + 1，1));}}//解析需求并将结果复制到Material和Qty列函数BuildMaterials(){var activeSheet = SpreadsheetApp.getActiveSheet();var selection = activeSheet.getSelection();//在开始之前，获取当前行和当前列，并将赋值分配给变量.job_row = activeSheet.getActiveCell().getRow();job_col = activeSheet.getActiveCell().getColumn();//获取这份工作的要求var text = selection.getCurrentCell().getValue();//用逗号分割，然后将需求放入数组中var textArray = text.split(，");//计算数组中的组件数var NumRequirements = textArray.length;for(var i = 0; i< NumRequirements; i ++){//建立一些变量，并确保前一个循环中的值不会继续var req_string =";var req_array = [];var req_data = [];var qty =";var material =";//获取组件；修剪以防万一req_string = textArray [i] .trim();//将组件放入其自己的数组(req_array)var req_array = req_string.split(");//获取数量和材料的值数量= req_array [0];材质= req_array [1];//将数量和材料类型的值分配给新数组.数量顺序和物料不同.req_data [0] =材料；req_data [1] =数量；//以Google表格所需的格式创建数组.var req_results = [req_data];//定义范围以复制结果ResultsRange = activeSheet.getRange((job_row + i)，(job_col + 1)，1，2);;//getRange(row，column，numRows，numColumns)//将材料和数量的值插入相关行的相应列中.ResultsRange.setValues(req_results);}//重复下一个要求//完成这项工作的要求.//将光标向下移动到下一个作业(行=需求数量)var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();var range = sheet.getRange((job_row + NumRequirements)，2，1，1);//getRange(row，column，numRows，numColumns)sheet.setActiveRange(range);} I'm fairly new to GDS and I'm loving it so far. What I'm trying to do is have a Google sheet containing requirements and quantity for a specific job, and then use the Data Studio to be able to search by the requirement and have the quantity in the same field.
I've mocked up some examples of what I currently have:
https://datastudio.google.com/open/1z0SIYa0ucpBiJXf_IQBaIsu61INP71Hc
https://docs.google.com/spreadsheets/d/1eUhE3chM77etyTcv4E7jyB_6vCFxSYArMyF2ptxK180/edit?usp=sharing
If I combine the quantity with the materials, I lose the ability to search by that material in the data studio. I'd like to be able to have just two columns and someone types in Mat X and all the jobs with that come up with the quantities in the same column.
I know I could accomplish something similar by having N amount of columns for each material and have that contain the quantity but is not practical for my application.
Thanks for you time.
Data Studio can't filter on Material because it can't identify the unique components.
I suggest that you create a second table in the spreadsheet that is created by a script/macro, and which lists the materials and quantities by job. This sheet is the source for Data Studio, and so it will enable filtering by Job or Material. The table can be updated at any time by running the macro.
The table might look like this:
The output in data Studio would look like this:
Here's a sample of Data Studio.
Were this Excel, the VBA would be easy to write, but Google Scripts uses Javascript so it will certainly take me a bit longer (though others would be far more adept) to provide a sample of code to achieve the new spreadsheet.
The Google Scripts logic is:
For each job
- count the number of commas in the requirements. Number of requirements = CommaCount plus one.
- Parse requirements to get qty and material details
- Copy first qty/materials into columns C & D for this job.
- For each additional requirements, duplicate the Job row, and copy the additional qty/materials into respective Columns C & D.
In Data Studio
- Insert Table; Dimension:Job, Requirement; Sort: Job Ascending.
- Insert Filter; Dimension: Job; Sort: Job Ascending.
- Insert Filter; Dimension: Material; Sort: Material Ascending.
UPDATE This code fulfils the objective outlined above - to have a second sheet that services the GDS. It takes the raw job information and builds a sheet of data, identifying each combination of material and quantity for each job. The code is probably not as efficient as it might be but the more important thing at this stage is - it works.
Three assumptions: 1) always more than one material per job; 2) material-code contains no spaces; 3) quantity is always an integer.
Quick Summary
There is one "master" function that can be assigned to a button on the GDS_data sheet. This makes rebuilding the GDS data extremely easy.
The Functions are:
- BuildGDSData - the main function
- CountJobs - counts, well...., the number of jobs
- CountMaterials - counts the number of requirements for each job
- CreateJobRows - inserts new rows so that there is one row per job per requirement
- BuildMaterials - parses the job requirements, and populates the "Material" and "Qty" columns for each row of the job.
The new Data Studio Page
// Convert Job info for GDS
function BuildGDSData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var source = ss.getSheetByName('jobinfo');// assumes basic job info is on sheet called "jobinfo"
var target = ss.getSheetByName('gds_data');// assumes that the GDS data is build on a sheet called "gds_data"
// Start gds_data from scratch. Delete everything
target.clearContents();
target.clearFormats();
// Get the data range from sheet = jobinfo
var rangeToCopy = source.getRange(1, 1, source.getMaxRows(),source.getMaxColumns());
// Paste the data to the cheet=gds_data
rangeToCopy.copyTo(target.getRange(1, 1));
// Add headings on sheet=gds_data for Material and Qty
target.getRange('C1').setValue('Material');
target.getRange('D1').setValue('Qty');
// Move the cursor to cell B2 on sheet = gds_data- this is the start of the job information
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var range = sheet.getRange('B2:B2');
sheet.setActiveRange(range);
// Call function CountJobs; assign number to jobs to variable "job_count"
var job_count = CountJobs();
// execute loop for the number of jobs
for (var i = 0; i < job_count; i++) {
// Call function CountMaterials; assign the number of materials for the job to variable MatCount
var MatCount = CountMaterials();
// Copy and insert new rows for the job so that there is one row per requirement. Copy the job details onto each new row
// Number of rows to create = for this job, the number of materials minus one. The "one" is row of job information that already exists.
CreateJobRows ((MatCount-1));
// Parse the requirements and copy the results to the Materials and Qty columns
BuildMaterials();
}
}
// Count and return the number of Jobs = number of rows of data minus one. the "one" is the header row,
function CountJobs() {
var numRows = SpreadsheetApp.getActiveSpreadsheet().getLastRow();
var numCols = SpreadsheetApp.getActiveSpreadsheet().getLastColumn();
return (numRows-1);
}
// Count and return the number of Materials for the current job; Quantity = number of commas plus 1
function CountMaterials(){
var activeSheet = SpreadsheetApp.getActiveSheet();
var selection = activeSheet.getSelection();
// get the requirments value for this job
var text = selection.getCurrentCell().getValue();
// use the split command to explode the requirments. Split by comma.
var textArray = text.split(",");
// Couht the number of components created by the split
var Num_Materials = textArray.length;
// Return the number of Components
return Num_Materials;
}
// Create new rows to cater for each requirment on the job. The count variable identifies the number of rows to create
function CreateJobRows(count) {
var sheet = SpreadsheetApp.getActiveSheet();
for (var i = 0; i < count; i++) {
// get the current row number
row = sheet.getActiveCell().getRow();
// insert a new row after the current row
sheet.insertRowAfter(row);
//copy the contents from cells A and B of the current row to the new row
var rangeToCopy = sheet.getRange(row, 1, 1, 2);
rangeToCopy.copyTo(sheet.getRange(row+1, 1));
}
}
// Parse the requirements and copy the results to the Materials and Qty columns
function BuildMaterials(){
var activeSheet = SpreadsheetApp.getActiveSheet();
var selection = activeSheet.getSelection();
// Before you start, get the current row and current column and assign the valuares to variables.
job_row = activeSheet.getActiveCell().getRow();
job_col = activeSheet.getActiveCell().getColumn();
// Get the requirements for this job
var text = selection.getCurrentCell().getValue();
// Split by comma and put the requirments into an array
var textArray = text.split(",");
// Count the number of components in the array
var NumRequirements = textArray.length;
for (var i = 0; i < NumRequirements; i++) {
// establish some variables and ensure that values from a previous loop don't carry over
var req_string = "";
var req_array = [];
var req_data = [];
var qty="";
var material = "";
// get the component; trim just in case
req_string = textArray[i].trim();
// put the component into its own array (req_array)
var req_array = req_string.split(" ");
// get values for quanity and material
qty = req_array[0];
material= req_array[1];
// assign values for quanty and material type to new array. Order of Qty and Material is different.
req_data[0] = material;
req_data[1] = qty;
// create array in format that Google Sheets requires.
var req_results = [req_data];
// define range to copy the results
ResultsRange = activeSheet.getRange((job_row+i), (job_col+1), 1, 2);; // getRange(row, column, numRows, numColumns)
// insert values for Materials and Qty into the respective columns on the relevant row.
ResultsRange.setValues(req_results);
} // repeat for next requirement
// Finished requirements for this job.
// Move the cursor down to the next job (rows=Number of Requirements)
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var range = sheet.getRange((job_row+NumRequirements), 2, 1, 1); // getRange(row, column, numRows, numColumns)
sheet.setActiveRange(range);
}
这篇关于在Google Data Studio中合并列数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
