Business
Jobs
  • About Us
  • Solutions
    • Job Postings
      Post your job and receive qualified candidates in 48h.
    • Candidate Assessments
      500+ technical and psychological tests, plus anti-fraud.
    • Headhunting
      Tailor-made executive search from start to finish.
    • Payroll + EOR
      Payroll dispersal and EOR across 15+ LATAM countries.
  • Pricing
  • Jobs

0

278
Views
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 answers
Answer question

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 Report

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 Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post vacancy Pricing Sales
Legal
Terms and conditions Privacy policy
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Show me some job opportunities
There's an error!