Query and transpose data in Google Sheets

Question

I have this a table that looks like this:

I want to create one line per year instead of one column per year, in a way it looks like this:

You can get the sample data in this google sheets.

I think it would be something using query or maybe even making a custom function, however I can't figure out how to do it (or to at least get close). Any suggestions?

Answer 1

I believe your goal as follows.

• You want to achieve the conversion of the table as shown in the images in your question using the built-in functions or the custom function.

For this, how about this answer? In this answer, I would like to propose to use the custom function. Each sample script retrieves the source values from the cells A2:H6. Please be careful this.

Pattern 1:

In this pattern, the values are put to the Spreadsheet using the custom function. Please copy and paste the following script to the script editor of the Spreadsheet. In this case, please put the custom function to a cell like =myFunction1().

Sample script:

function myFunction1() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const values = sheet.getRange("A2:H6").getValues();
  const headers = values.shift().splice(2);
  const res = values.reduce((ar, r) => {
    let temp = r.splice(0, 2);
    for (let i = 0; i < headers.length; i += 2) {
      let h = temp;
      ar.push(h.concat(headers[i].split("Revenues")[1].trim(), r.splice(0, 2)));
    }
    return ar;
  }, []);
  res.unshift(["Company", "Company code", "Year", "Revenues", "Costs"]);
  return res;
}

Pattern 2:

Please copy and paste the following script to the script editor of the Spreadsheet. When I saw your sample Spreadsheet, the columns of "Revenues" and "Costs" in the converted table have the cell coordinates of the original table. So in this pattern, the goal of your sample Spreadsheet is replicated. But, the custom function cannot directly put the formula. So in this pattern, this script is not used as the custom function. So in order to test this, please run this with the script editor.

Sample script:

function myFunction2() {
  // Ref: https://stackoverflow.com/a/21231012/7108653
  const columnToLetter = column => {
    let temp,
      letter = "";
    while (column > 0) {
      temp = (column - 1) % 26;
      letter = String.fromCharCode(temp + 65) + letter;
      column = (column - temp - 1) / 26;
    }
    return letter;
  };

  const sheet = SpreadsheetApp.getActiveSheet();
  const values = sheet.getRange("A2:H6").getValues();
  const headers = values.shift().splice(2);
  const res = values.reduce((ar, r, i) => {
    let temp = r.splice(0, 2);
    for (let j = 0; j < headers.length; j += 2) {
      let h = temp;
      const formulas = [`=${columnToLetter(j + 3)}${i + 3}`, `=${columnToLetter(j + 4)}${i + 3}`];
      ar.push(h.concat(headers[j].split("Revenues")[1].trim(), formulas));
    }
    return ar;
  }, []);
  res.unshift(["Company", "Company code", "Year", "Revenues", "Costs"]);
  sheet.getRange(23, 1, res.length, res[0].length).setValues(res);
}

• In this script, when you run the function of myFunction2 with the script editor, the result values are put from the cell "A23".

Note:

• If you don't want to put the header row, please remove res.unshift(["Company", "Company code", "Year", "Revenues", "Costs"]);.
• Please use these script with V8.

References:

• Custom Functions in Google Sheets
• reduce()