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

user_15180040 0
Viewed 139

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

google-apps-script google-sheets javascript
2 Answers

Please take a look at the code snippet below it should help. I've just created a hash of how many times a company occurs in the spreadsheet and then using this hash I've updated the initial data.

const data = [
  {company: "A", turnover: 10000},
  {company: "A", turnover: 10000},
  {company: "A", turnover: 10000},
  {company: "A", turnover: 10000},
  {company: "B", turnover: 20000},
  {company: "B", turnover: 20000},
]

const hash = {}

for (let d of data) {
  if (d.company in hash) hash[d.company] += 1
  else hash[d.company] = 1
}

const updatedData = data.map(d => ({...d, turnover: d.turnover/hash[d.company] }))

console.log(updatedData)

You can use Map as well, which is what I would suggest!

const data = [
  {company: "A", turnover: 10000},
  {company: "A", turnover: 10000},
  {company: "A", turnover: 10000},
  {company: "A", turnover: 10000},
  {company: "B", turnover: 20000},
  {company: "B", turnover: 20000},
]

const hash = new Map();

for (let d of data) {
  const currentVal = hash.get(d.company);
  if (currentVal) hash.set(d.company, currentVal + 1)
  else hash.set(d.company, 1)
}

const updatedData = data.map(d => ({ ...d,
  turnover: d.turnover / hash.get(d.company)
}))

console.log(updatedData)

user_117193140

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:

user_71086530
Related