Is it possible to restructure table data with google-app script coding

Table data is currently

I need to reformat the on a second sheet to look like this

Is there app-script coding that can restructure the table to the above?

I am trying to build a Google Combo Bar Chart, so I need the languages as column headings. This way I can add them to the google charting feature as a "series".

At this point I can only think of a manual solution to parse out the data.

I tried using pivot tables to re-shape things, but it fell short.

I believe your goal is as follows.

• You want to convert the top table to the bottom table in your question.
• You want to achieve this using Google Apps Script.
• In your showing tables, "Ukrainian" and "Ukrain" are used in the top and bottom tables, respectively. In this answer, "Ukrainian" is used for both situations. Please be careful about this.

The sample script is as follows.

Sample script:

Please copy and paste the following script to the script editor of Google Spreadsheet and set your source and destination sheet names on the Google Spreadsheet, and save the script.

function myFunction() {
  const srcSheetName = "Sheet1"; // Please set the source sheet name.
  const dstSheetName = "Sheet2"; // Please set the destination sheet name.

  const dstHeader = ["Titles", "German", "Ukrainian", "English", "Spanish", "Russian"]; // This is from your expected result in your question.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const srcSheet = ss.getSheetByName(srcSheetName);
  const dstSheet = ss.getSheetByName(dstSheetName);
  const [, ...srcValues] = srcSheet.getDataRange().getValues();
  const values = [dstHeader, ...srcValues.map(([a, b, c]) => {
    const temp = Array(dstHeader.length - 1).fill(0);
    const idx = dstHeader.indexOf(b);
    if (idx > -1) {
      temp.splice(idx - 1, 1, c);
    }
    return [a, ...temp];
  })];
  dstSheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}

Testing:

When this script is run, the following result is obtained. It seems that this result is the same as your bottom table except for the spelling of "Ukrain" in your question.

Note:

• I have only the information from your question. So, when your actual situation is different from your showing tables, this script might not be able to be used. Please be careful about this.

References:

• map()

• splice()

<!-- language-all: js -->

Just in case you are open to a plain formula solution and not exclusively tied to script-based solution(for X reasons); you could give this a shot to restructure data & create the end-chart

=query({A2:C},"select Col1, sum(Col3) where Col1<>'' group by Col1 pivot Col2")

Alternate formula:

=let(row,unique(tocol(A2:A,1)),
     col,unique(torow(B2:B,1),1),
     vstack(hstack("Titles",col),{row,map(row,lambda(Σ,bycol(col,lambda(Λ,let(Γ,sumifs(C:C,A:A,Σ,B:B,Λ),if(Γ>0,Γ,))))))}))

• the formula(s) are dynamic(auto-expands downwards + right ways) as new titles /or languages add up in the raw data accommodated in columns A:C