Empresas
Empregos
  • Sobre nós
  • Soluções
    • Publicação de vagas
      Publique sua vaga e receba candidatos qualificados em 48h.
    • Avaliações de candidatos
      Mais de 500 testes técnicos e psicológicos, mais anti-fraude.
    • Headhunting
      Busca executiva personalizada do início ao fim.
    • Folha de Pagamento + EOR
      Dispersão de folha e EOR em mais de 15 países da LATAM.
  • Preços
  • Empregos

0

273
Visualizações
Google Apps Script Sheets PasteDataRequest paste to column instead of row?

My current script pastes the values just fine but it pastes them across the row from the data coordinate and I need it to paste down the column. How do I do that?

Example data: Car, cat, bat, rat

How it’s outputting:

| Car | cat | bat | rat |

How it needs to be output:

Car |
___ 
cat |
___
bat |
___
rat |

Example code snippet:

var gridCoordinate = Sheets.newGridCoordinate()
gridCoordinate.sheetID = sheet.getSheetID()
gridCoordinate.rowIndex = "1"
gridCoordinate.columnIndex = "1"

let pasteRequest = Sheets.newPasteDataRequest()
pasteRequest.data = inputValues
pasteRequest.type = SpreadsheetApp.CopyPasteType.PASTE_VALUES
pasteRequest.coordinate = gridCoordinate 
pasteRequest.delimiter = ","

let requests = [Sheets.newRequest()]
requests[0].pasteData = pasteRequest;

let batchUpdateRequest = Sheets.newBatchUpdateSpreadsheetRequest();
batchUpdateRequest.requests = requests

var result = Sheets.Spreadsheets.batchUpdate(batchUpdateRequest, "REDACTED")
Logger.log(result)

Edit to add: inputValues has to be a comma delimited string per the documentation on PasteDataRequests

about 4 years ago · Santiago Gelvez
2 Respostas
Responde à pergunta

0

You don't specify how the array is created but assuming its a 1D array ["Car", "cat", "bat", "rat"] it needs to be transformed into a 2D array using testArray() [["Car"], ["cat"], ["bat"], ["rat"]].

If its a string like a csv try testString() where each row is seperated by a new line \n and each cell by a comma.

Code.gs

function testArray() {
  try {
    let a = ["Car", "cat", "bat", "rat"];
    let b = a.map( c => [c] );
    console.log(a);
    console.log(b);
  }
  catch(err) {
    console.log(err);
  }
}

function testString() {
  try {
    let a = "Car\ncat\nbat\nrat";
    let b = Utilities.parseCsv(a);
    console.log(b);
  }
  catch(err) {
    console.log(err);
  }
}

Execution log

9:21:32 AM  Notice  Execution started
9:21:32 AM  Info    [ 'Car', 'cat', 'bat', 'rat' ]
9:21:32 AM  Info    [ [ 'Car' ], [ 'cat' ], [ 'bat' ], [ 'rat' ] ]
9:21:32 AM  Notice  Execution completed

3:12:37 PM  Notice  Execution started
3:12:38 PM  Info    [ [ 'Car' ], [ 'cat' ], [ 'bat' ], [ 'rat' ] ]
3:12:38 PM  Notice  Execution completed
about 4 years ago · Santiago Gelvez Relatório

0

From There is not more than 2 rows. I gave the expected output in my original question., it supposes that when your sample value is Car, cat, bat, rat, in order to achieve your goal, how about the following modification?

Modification points:

  • When I asked What is sheet of sheet.getSheetID()?, you said var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"). In this case, sheet.getSheetID() occurs an error because getSheetID should be getSheetId. And also, an error occurs at gridCoordinate.sheetID. Becuase, in this case, gridCoordinate.sheetID is required to be gridCoordinate.sheetId.
  • In order to put Car, cat, bat, rat to a column using your script, replace(/,/g, "\n") is used.

When these points are reflected to your script, it becomes as follows.

Modified script:

var inputValues = "Car, cat, bat, rat"; // This sample value is from your question.
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // This is from your reply.

var gridCoordinate = Sheets.newGridCoordinate();
gridCoordinate.sheetId = sheet.getSheetId(); // Modified
gridCoordinate.rowIndex = "1";
gridCoordinate.columnIndex = "1";

let pasteRequest = Sheets.newPasteDataRequest();
pasteRequest.data = inputValues.replace(/,/g, "\n"); // Modified
pasteRequest.type = SpreadsheetApp.CopyPasteType.PASTE_VALUES;
pasteRequest.coordinate = gridCoordinate;
pasteRequest.delimiter = ",";

let requests = [Sheets.newRequest()];
requests[0].pasteData = pasteRequest;

let batchUpdateRequest = Sheets.newBatchUpdateSpreadsheetRequest();
batchUpdateRequest.requests = requests;
var result = Sheets.Spreadsheets.batchUpdate(batchUpdateRequest, "REDACTED");
Logger.log(result)
  • When this script is run, the values of Car, cat, bat, rat are put to the cells "B2:B5" of the active sheet.

Note:

  • In your situation, when you want to use Sheets API, I thought that you also might be able to use the following sample.

      Sheets.Spreadsheets.Values.update({ values: Utilities.parseCsv(inputValues), majorDimension: "COLUMNS" }, "REDACTED", "Sheet1!B2", { valueInputOption: "USER_ENTERED" });
    

Reference:

  • PasteDataRequest
about 4 years ago · Santiago Gelvez Relatório
Responde à pergunta
Encontrar trabalhos remotos

Descubra a nova forma de encontrar um emprego!

melhores empregos
Principais categorias de trabalho
Empresas
Postar vaga Preços Comercial
Jurídico
Termos e Condições Política de privacidade
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomende algumas ofertas para mim
Preciso de ajuda