Adding Array from Variable on Google Sheets - Google Apps Script

Question

I have an existing code which looks like,

var copyPageNumbers = [5, 7, 9];

I have the same number 5, 7, 9 in a column on my google sheet and have set up a variable to get value,

var credentials = ss.getRange(i, 11).getValue(); // The value of this is 5, 7, 9

Based on the variables above. I want to use a code which uses the credentials variable in the copyPageNumbers variable to look something like this,

var copyPageNumbers = [credentials];

For example, the below chunk of code works,

  var credentials = ss.getRange(i, 11).getValue();
  var copyPageNumbers = [2, 4];

  var offset = 1;

  var slides = otherPresentation.getSlides();
  var page = 0;
  slides.forEach(function(slide, i) {
    if (copyPageNumbers.indexOf(i + 1) != -1) {
      currentPresentation.insertSlide(offset + page, slide);
      page++;
    }
  });

However when I try both the methods stated below - it doesnt work,

  var credentials = ss.getRange(i, 11).getValue().split(", ");
  var copyPageNumbers = credentials
  var offset = 1;
  var slides = otherPresentation.getSlides();
  var page = 0;
  slides.forEach(function(slide, i) {
    if (copyPageNumbers.indexOf(i + 1) != -1) {
      currentPresentation.insertSlide(offset + page, slide);
      page++;
    }
  });

  var credentials = ss.getRange(i, 11).getValue();
  var offset = 1;
  var copyPageNumbers = []
  copyPageNumbers.push(credentials);
  var slides = otherPresentation.getSlides();
  var page = 0;
  slides.forEach(function(slide, i) {
    if (copyPageNumbers.indexOf(i + 1) != -1) {
      currentPresentation.insertSlide(offset + page, slide);
      page++;
    }
  });

Diego · Accepted Answer · 2020-02-13 09:11:07Z

2

var credentials = ss.getRange(i, 11).getValue(); return the string "5, 7, 9", but you need that as an array.

Assuming that the format is consistent (i.e., [number][comma][space]), you can use split() to turn it into an array.

var credentials = ss.getRange(i, 11).getValue().split(", "); // [5, 7, 9]
var copyPageNumbers = credentials;

answered Feb 13, 2020 at 9:11

Diego

9,5812 gold badges24 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mishal Over a year ago

thanks @Diego however this method is not working with my script. Please see edits made to question.

Diego Over a year ago

@MohammedMishal What are the outputs of Logger.log(ss.getRange(i, 11).getValue()) and Logger.log(ss.getRange(i, 11).getValue().split(", "))?

Mishal Over a year ago

here are the outputs [20-02-14 20:08:37:151 GST] 3, 4 and [20-02-14 20:08:37:248 GST] [3, 4] even though it output look like they are an array the script to include slides 3 and 4 don't seem to work compared to when I hard key the numbers 3 and 4 into the script.

timmer · Accepted Answer · 2020-02-13 11:04:41Z

2

var copyPageNumbers = [];
var credentials = ss.getRange(i, 11).getValue();
copyPageNumbers.push(credentials);

You want an array ([5,7,9]), 'credentials' is a string (5,7,9). With the (javascript) push() method that string is added to the array 'copyPageNumbers'. If I understand it well.

edited Feb 13, 2020 at 11:04

answered Feb 13, 2020 at 9:48

timmer

1596 bronze badges

7 Comments

timmer Over a year ago

The OP(?) wants (I think) an array, 'credentials' is a string, not an array. With the (javascript) push() method the string is added to the array 'copyPageNumbers'. If I understand it well.

Mishal Over a year ago

thanks @timmer however this method is not working with my script. Please see edits made to question.

timmer Over a year ago

@MohammedMishal (1) In 'my' answer var credentials = ss.getRange(i, 11).getValue(); (2) Should '!=' in the if thing not be '!==' ?

Mishal Over a year ago

@TimmerI have done that and it still doesn't work compared to when I hard code the numbers in the array. would be grateful if you could share any alternative ways

timmer Over a year ago

@MohammedMishal >> Perhaps it does not work because 'offset = 1' is missing in 'our' answers

|

Mishal · Accepted Answer · 2020-02-15 00:14:47Z

0

Thanks to the above answers and thanks to Pattern 2 here, I have managed to solve this.

See updated script below. The reason it was not working before was because of the For each loop which was looping the slide and the number and the array was not working.

  var credentials = ss.getRange(i, 11).getValue().split(", ");

  var offset = 0;

  var slides_stack = otherPresentation.getSlides();
  var page = 0;

    credentials.forEach(function(p, i) {
    currentPresentation.insertSlide(offset + i, slides_stack[p - 1]);
  });

answered Feb 15, 2020 at 0:14

Mishal

43010 silver badges30 bronze badges

Collectives™ on Stack Overflow

Adding Array from Variable on Google Sheets - Google Apps Script

3 Answers 3

3 Comments

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related