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

CodePudding user response：

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

CodePudding user response：

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