How to reverse the order of the columns in google sheet after extracting the data from another sheet

0
votes

So I extracted data from one google sheets to another google sheet using google sheets API successfully. Now i want to reverse the order of the columns, for example want column A with K, B with J, and for on. This is my code for now:

function authenticate() {
return gapi.auth2.getAuthInstance()
.signIn({scope: "https://www.googleapis.com/auth/drive https://www.googleapis.com/auth/drive.file https://www.googleapis.com/auth/spreadsheets"})
.then(function() { console.log("Sign-in successful"); },
      function(err) { console.error("Error signing in", err); });
function loadClient() {
gapi.client.setApiKey("");
.then(function() { console.log("GAPI client loaded for API"); },
          function(err) { console.error("Error loading GAPI client for API", err); });
  // Make sure the client is loaded and sign-in is complete before calling this method.
function execute() {
return gapi.client.sheets.spreadsheets.sheets.copyTo({
  "spreadsheetId": "",
  "sheetId": ,

 "resource": {
    "destinationSpreadsheetId": ""
  }
})
.then(function(response) {
            // Handle the results here (response.result has the parsed body).
            console.log("Response", response);
          },
          function(err) { console.error("Execute error", err); });
("client:auth2", function() {
gapi.auth2.init({client_id: ""});
});

How can I edit this code to put the columns in reverse order using code? Here is a link to my spreadsheet: https://docs.google.com/spreadsheets/d/1BJ0Y8viJE8a2jU6rfNGuNp5n1jwQauee1p9-PxuuVHE/edit#gid=533169457

1
javascriptgoogle-sheetsgoogle-sheets-api
Did my answer show you the result what you want? Would you please tell me about it? That is also useful for me to study. If this works, other people who have the same issue with you can also base your question as a question which can be solved. If you have issues for my answer yet, I apologize. At that time, can I ask you about your current situation? I would like to study to solve your issues. – Tanaike

1 Answers

0
votes

I believe your goal and current situation as follows.

  • You want to put the values from the source sheet to the destination sheet by selecting the specific columns from the source sheet using googleapis for Javascript.
    • In your case, as a sample, you want to select the columns "A,K,B,J" from the source sheet and put them to the destination sheet.
  • You have already been get and put values for Google Spreadsheet using Sheets API using your current script.

Modification points:

  • In order to achieve your goal, I would like to propose the following flow.
    1. Retrieve the values from the source sheet using spreadsheets.values.get.
    2. Extract the specific columns from the retrieved values.
    3. Put the extracted values to the destination sheet using spreadsheets.values.update.

When this flow is reflected to your script, it becomes as follows.

Modified script:

Before you use this script, please set the variables of spreadsheetId, sourceSheetName, destinationSheetName and extractColumns.

const spreadsheetId = "###";
const sourceSheetName = "###";
const destinationSheetName = "###";
const extractColumns = [1, 11, 2, 10];  // "A,K,B,J" in order.

const transpose = (ar) => ar[0].map((_, i) => ar.map(r => r[i]));
gapi.client.sheets.spreadsheets.values.get({
  spreadsheetId: spreadsheetId,
  range: sourceSheetName
}).then((res)=>{
  const transposedValues = transpose(res.result.values);
  const extractedColumns = extractColumns.map(c => transposedValues[c - 1]);
  const resultValues = transpose(extractedColumns);
  gapi.client.sheets.spreadsheets.values.update({
    spreadsheetId: spreadsheetId,
    range: destinationSheetName,
    valueInputOption: "USER_ENTERED"
  }, {
    values: resultValues
  }).then((r) => {
    console.log(res.result.values)
  }, function(er) {
    console.error(er.result.error.message);
  })
}, function(err) {
  console.error(err.result.error.message);
});

References: