CODEWITHSUNDEEP
javascriptgoogle-sheetsgoogle-sheets-api
craigreska
craigreska

Reputation: 1

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

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

Upvotes: 0

Views: 354

Answers (1)

Tanaike
Tanaike

Reputation: 201428

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:

Upvotes: 0

Related Questions