Skip to content
Advertisement

How to use apps script to count cells number and change the context in other column based on it?

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’.Here is the code

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.
Result:

When above script is used for your sample Spreadsheet, the following result is obtained.

enter image description here

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] : ""]);
      }
    

References:

Advertisement