CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Office Welwyn Parish Council
Office Welwyn Parish Council

Reputation: 31

Google Apps Script - setvalues not working on sheet

OK so I seem to be stumped by setValue() and setValues() I understand what they are, but can't get it to work. No values appear to be set! (This is part of a bigger script project to generate Invoices and Schedules from Google Calendar.)

function Version3() {
// STEPXX - First create a copy of the template
// Choose the source
var RHTemplate = 'MY SHEET ID'
var RHCode = "HirerCode"
// Use the variable for the Billing Prefix as it should be unique to create the folder.
var RHFolder = DriveApp.createFolder("BillPrefix").getId()
var RHFolderID = DriveApp.getFolderById(RHFolder);
// Now copy the template file
var RHgetTemplate = DriveApp.getFileById(RHTemplate);
// var RHFileName = (SpreadsheetApp.openById(RHTemplate).rename(RHCode));
var RHFile = RHgetTemplate.makeCopy(RHCode, RHFolderID);
var RHss = SpreadsheetApp.open(RHFile);
var RHsheetInv = RHss.getSheetByName('Invoice') //.getSheets()[0]; get by sheet index
var RHCell = RHsheetInv.getRange(1, 1);
RHCell.setValues([RHCode]);
debugger
}

I just can't see why it's not writing the words "Hirer Code" in the first cell on the sheet called "Invoice" in the spreadsheet called "Hirer Code" in the folder called "BillPrefix".

Any and all help appreciated!

Upvotes: 0

Views: 4785

Answers (5)

Iúri Batista Teles
Iúri Batista Teles

Reputation: 101

You can use setFormula method.

For example:

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange(1, 7); //get the cell here
range.setFormula('"'+myValueHere+'"');

Upvotes: 0

Sean Johnson
Sean Johnson

Reputation: 11

It seems that setValues() works asynchronously when in debug mode, because when you stop the code the values are written to the sheet.

Upvotes: 1

Office Welwyn Parish Council
Office Welwyn Parish Council

Reputation: 31

For others having these issues I finally tied it down to an anomaly in debugging. When you run as debug, SOMETIMES it can setValue/setValues, sometimes it fails. It runs without issue however when you just run the function as normal. Similar issues with user interface pop up boxes!

Upvotes: 3

Allyn
Allyn

Reputation: 20441

The issue is probably one of dimensionality. Set values deals with a 2-dimensional array. Your example provides a 1-dimensional array.

Original:

var RHCell = RHsheetInv.getRange(1, 1);
RHCell.setValues([RHCode]);

Corrected:

var RHCell = RHsheetInv.getRange(1, 1);
RHCell.setValues([ [ RHCode ] ]);

Upvotes: 1

ScampMichael
ScampMichael

Reputation: 3728

If it's a single value you are setting you do not need to worry about arrays. Use setValue() without the s.

Replace:

var RHCell = RHsheetInv.getRange(1, 1);
RHCell.setValues([RHCode]);

with:

RHsheetInv.getRange(1, 1).setValue(RHCode);

Upvotes: 2

Related Questions