0

I am new to the Google script. And I have not much knowledge from other codes. Still, I wish I had a script to export my schedule from a Spreadsheet to Google Calendar. I've been able to create a script based on this topic: Create Google Calendar Events from Spreadsheet but prevent duplicates

The only problem is that my functions I've made in my spreadsheet disappear when I run the script. Is there a solution?

Community
  • 1
  • 1
Jan Verhoeven
  • 3
  • 2

1 Answers1

1

The problem is that getValues() doesn't get formulas, only their result, you need getFormulas() also, but this one doesn't get the values only cells. Solution: before starting everything, merge up the formulas and values into a single var, as such:

var data = range.getValues();    
var formulas = range.getFormulas();
  for( line in data )
    for( column in data[i] )
      if( formulas[ line ][ column ] != '' )
        data[ line ][ column ] = formulas[ line ][ column ];

And only then proceed with the normal for(i in...).

Munkey
  • 958
  • 11
  • 28
Kriggs
  • 3,731
  • 1
  • 15
  • 23
  • @JanVerhoeven if this solved you problem please mark as Answered just below the down arrow, if this question was well explained, upvote it in the up arrow. – Kriggs Apr 23 '15 at 12:28
  • @Kriggs I've always struggled writing both formula and values at the same time. I'll have to have a play with the code above and see what I can get working. If you do have a bit of time free, I'd really appreciate your comments on how your answer works. I get that it's reading both formula & values. But then how to use next. How to decide to write a formula or value. As I don't Fully understand the source script of this question, your answer is a little out of context for me (if that makes sense, nothing to do with your answer, just my understanding) – Munkey Apr 24 '15 at 12:43
  • 1
    @Munkey getFormulas() return an Array just as getValues(), same size, but when there's no formula in the cell, it returns an empty String `''`. I just checked for when there's a formula (ie. not empty), I'll use it by placing that formula in the getValues() original Array. The above 2 fors are just to iterate all the 2 dimensions of the Array. – Kriggs Apr 24 '15 at 13:33