Currently, I have three columns in my google sheets, column 1 is the company name, column 2 is industries name and column 3 is turnover (but the data is repeated). And I want to count the same company name and divide the turnover based on that number use apps script.
Here are the example and result I want.
This is my original spreadsheet. https://docs.google.com/spreadsheets/d/1CPMXkOKzOjGEwcR7OdK7SCEO2Nri_DlN_GBoMLSwtTA/edit?usp=sharing
When I tried to use this script, there is an error that showed ‘values is not defined’.
Advertisement
Answer
I believe your goal as follows.
You want to achieve the following situation. (The following image is from your question.)
You want to achieve this using Google Apps Script.
In this case, how about the following sample script?
Sample script:
Please copy and paste the following script to the script editor of Spreadsheet and please run the function of myFunction. By this, the result values are put to the column “D”.
function myFunction() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
const range = sheet.getRange("A2:C" + sheet.getLastRow());
const values = range.getValues();
const obj = values.reduce((o, [a]) => Object.assign(o, {[a]: (o[a] ? o[a] + 1 : 1)}), {});
const newValues = values.map(([a,,c]) => [c / obj[a]]);
range.offset(0, 3, newValues.length, 1).setValues(newValues);
}
- In this script, your sample Spreadsheet is used. So please be careful this.
- When you want to put the result values to other range, please modify above script.
When above script is used for your sample Spreadsheet, the following result is obtained.
Note:
In your case, I think that your goal can be also achieved using the custom function. If you want to use the custom function, please use the following script. Please copy and paste the following script to the script editor of Spreadsheet and please put a formula of
=SAMPLE(A2:C)to a cell. By this, the result values are put to the cells.function SAMPLE(values) { const obj = values.reduce((o, [a]) => Object.assign(o, {[a]: (o[a] ? o[a] + 1 : 1)}), {}); return values.map(([a,,c]) => [a ? c / obj[a] : ""]); }