Empresas
Empleos
  • Sobre nosotros
  • Soluciones
    • Publicación de vacantes
      Publica tu vacante y recibe candidatos calificados en 48h.
    • Evaluación de candidatos
      500+ pruebas técnicas y psicológicas, más anti-fraude.
    • Headhunting
      Búsqueda ejecutiva a la medida de principio a fin.
    • Nómina + EOR
      Dispersión de nómina y EOR en más de 15 países de LATAM.
  • Precios
  • Empleos

0

274
Vistas
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 Respuestas
Responde la pregunta

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 Denunciar

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 Denunciar
Responde la pregunta
Encuentra empleos remotos

¡Descubre la nueva forma de encontrar empleo!

Top de empleos
Top categorías de empleo
Empresas
Publicar vacante Precios Comercial
Legal
Términos y condiciones Política de privacidad
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomiéndame algunas ofertas
Necesito ayuda