0

I'd like to have valuesToCopy be separated by a space where the commas are created by getDisplayValues() in the popup so it doesn't appear as one long string. I'm using getDisplayValues() because there are dates that I don't want to change the formatting for.


function TextBox() {
  var ui = SpreadsheetApp.getUi();
  const sheet = SpreadsheetApp.openById('1JZ-v5n5m0_lyaoLgsHDAa78AtYNP5AsUDo2NRpJbwG4').getSheetByName('HOT WARM CLIENTS');
  sheet.sort(9)
  var today = Utilities.formatDate(new Date(), "GMT+1", "MM/dd/yy")
  var ui = SpreadsheetApp.getUi();
  var valuesToCopy = sheet.getRange("D5:J5").getDisplayValues()

var ar2 = [
{
  message: valuesToCopy + "\n\n Did we do work for this client today/yesterday?",
  range: "G5"
}

];

ar2.forEach(({message, range }) => {
  var res = ui.prompt(message, ui.ButtonSet.YES_NO);
  if (res.getSelectedButton() == ui.Button.YES) {
    sheet.getRange(range).setValue(res.getResponseText());
    SpreadsheetApp.getUi();
  } else { 
    Logger.log('The user clicked "No" or the dialog\'s close button.');
  }
})

I've tried

valuesToCopy = valuesToCopy.split(',') 
and I get an error: TypeError: valuesToCopy.split is not a function
Ricardo Barrios
  • 29
  • 9
  • `valuesToCopy` is a array. Use [Array.map](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/map). Also see https://stackoverflow.com/questions/63720612/what-does-the-range-method-getvalues-return-and-setvalues-accept – TheMaster Sep 27 '22 at 20:25
  • so do you wish the 2d array of values to be turned into a flat array of strings? – Cooper Sep 27 '22 at 20:30
  • Ideally they would appear in a column with the contents of each cell one on top of the other but I'm okay with a flat array of strings as long as there are spaces – Ricardo Barrios Sep 27 '22 at 20:34

1 Answers1

1
var valuesToCopy = sheet.getRange("D5:J5").getDisplayValues().map(r => r.join(' '));
Cooper
  • 59,616
  • 6
  • 23
  • 54
  • I should have mentioned I was looking for how to make it a column specifically so I used var valuesToCopy = sheet.getRange("D5:J5").getDisplayValues().map(r => r.join(' \n ')); – Ricardo Barrios Sep 27 '22 at 20:40
  • How can I change the text size of all the text in the popup? – Ricardo Barrios Sep 27 '22 at 20:41
  • If you pop is a SpreadsheetApp.getUi() dialog then you can use css. If you popup is a toast I don't believe that you can. – Cooper Sep 27 '22 at 21:15