dynamically save data to spreadsheet

[voronchuk]

dynamically save data to spreadsheet

[Voronchuk]

It works as intended, please use ‘append_rows’ in case you don’t wish to override existing data or calculate the row offset manually if it works better for you use case.

…

On Thu, Dec 26, 2019 at 13:34 p_pilipovic ***@***.***> wrote: Hey, how can someone dynamically save data into the spreadsheet, current example which I'm running will save the data in 20 rows GSS.Spreadsheet.write_rows(pid, [ "A2:I2", "A3:I3", "A4:I4", "A5:I5", "A6:I6", "A7:I7", "A8:I8", "A9:I9", "A10:I10", "A11:I12", "A13:I13", "A14:I14", "A15:I15", "A16:I16", "A17:I17", "A18:I18", "A19:I19", "A20:I20", ], [[...], [...]]...) but every time I save it again it will just override the last save, so what will be the proper way of overcoming this obstacle? Thanks — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#41?email_source=notifications&email_token=AADFG3KUE77ISSXIQ7K7KILQ2SJENA5CNFSM4J7KYA2KYY3PNVWWK3TUL52HS4DFUVEXG43VMWVGG33NNVSW45C7NFSM4ICWLK2Q>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AADFG3PTU47U63CKXVVDYMLQ2SJENANCNFSM4J7KYA2A> .

[Copser]

@Voronchuk sure calculating index will be fine, I was just wondering about the options part of the append_rows I don't see any docs about it, and I'm currently facing this error

** (FunctionClauseError) no function clause matching in GSS.Spreadsheet.append_rows/4

    The following arguments were given to GSS.Spreadsheet.append_rows/4:

        # 1
        #PID<0.620.0>

        # 2
        1204476179719294976

        # 3
        [592700022, 1207314578675126272, 592700022, "", "CandyCrushSaga",
         "Candy Crush Saga", "2019-12-18 15:00:01.000Z",
         "Today we're celebrating more than just new levels; today we hit level 6000! \nShare below if you've reached that milestone yet! pic.twitter.com/HxSj0GpUpk",
         ""]

        # 4
        [pad_empty: true]

    Attempted function clauses (showing 1 out of 1):

        def append_rows(pid, row_index, [[cell | _] | _] = data, options) when (is_binary(cell) or cell == nil) and row_index > 0

    (elixir_google_spreadsheets) lib/elixir_google_spreadsheets/spreadsheet.ex:215: GSS.Spreadsheet.append_rows/4
    (elixir) lib/enum.ex:783: Enum."-each/2-lists^foreach/1-0-"/2
    (elixir) lib/enum.ex:783: Enum.each/2
    (twitter_feed) lib/spreadsheet.ex:36: SpreadSheet.save_to_spreadsheet/2

what will be prefered options for this? If I understand this error correctly It is telling me that some rows will be empty which is fine I guess, so not quite sure what is happening.

[Voronchuk]

Hi, this one is easy, just use strings instead of integers “592700022”, google spreadsheets save everything as string so there is no automatic serialization in this low-level library. Personally, I’m using ecto_gss for serialization / unserialization, but you can just do this manually.

…

On Fri, Dec 27, 2019 at 07:54 p_pilipovic ***@***.***> wrote: @Voronchuk <https://github.com/Voronchuk> sure calculating index will be fine, I was just wondering about the options part of the append_rows I don't see any docs about it, and I'm currently facing this error ** (FunctionClauseError) no function clause matching in GSS.Spreadsheet.append_rows/4 The following arguments were given to GSS.Spreadsheet.append_rows/4: # 1 #PID<0.620.0> # 2 1204476179719294976 # 3 [592700022, 1207314578675126272, 592700022, "", "CandyCrushSaga", "Candy Crush Saga", "2019-12-18 15:00:01.000Z", "Today we're celebrating more than just new levels; today we hit level 6000! \nShare below if you've reached that milestone yet! pic.twitter.com/HxSj0GpUpk", ""] # 4 [pad_empty: true] Attempted function clauses (showing 1 out of 1): def append_rows(pid, row_index, [[cell | _] | _] = data, options) when (is_binary(cell) or cell == nil) and row_index > 0 (elixir_google_spreadsheets) lib/elixir_google_spreadsheets/spreadsheet.ex:215: GSS.Spreadsheet.append_rows/4 (elixir) lib/enum.ex:783: Enum."-each/2-lists^foreach/1-0-"/2 (elixir) lib/enum.ex:783: Enum.each/2 (twitter_feed) lib/spreadsheet.ex:36: SpreadSheet.save_to_spreadsheet/2 what will be prefered options for this? If I understand this error correctly It is telling me that some rows will be empty which is fine I guess, so not quite sure what is happening. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#41?email_source=notifications&email_token=AADFG3MSE6YCO75DLWBW5KLQ2WJ7JA5CNFSM4J7KYA2KYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEHWTYDQ#issuecomment-569195534>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AADFG3NP4SZ6PUXZTWJXOMTQ2WJ7JANCNFSM4J7KYA2A> .

[Copser]

Hey ok but this is the struct which I want to save to the spreadsheet.

%TwitterFeed.Feed{
  tweets: [
    %TwitterFeed.Tweet{
      display_name: "Candy Crush Saga",
      handle_id: "592700022",
      image_url: "",
      text_summary: "We've got a gift for you...TWICE the number of New Levels! Let us know how you spent your day playing below! pic.twitter.com/eAVPxSAH2d",
      timestamp: "2019-12-25 15:00:00.000Z",
      tweet_id: "1209851293331841024",
      tweet_lng: "",
      user_id: "592700022",
      user_name: "CandyCrushSaga"
    },
    %TwitterFeed.Tweet{
      display_name: "Candy Crush Saga",
      handle_id: "592700022",
      image_url: "https://pbs.twimg.com/media/EMuUH3eWwAMVVvv.jpg",
      text_summary: "Earn points and get Sweet rewards this Holiday season! You can get x4 Google #PlayPoints on everything you buy in the Year End Deals in #CandyCrush before Decem...",
      timestamp: "2019-12-26 15:40:11.000Z",
      tweet_id: "1210223789805637634",
      tweet_lng: "",
      user_id: "592700022",
      user_name: "CandyCrushSaga"
    },
    %TwitterFeed.Tweet{
      display_name: "Candy Crush Saga",
      handle_id: "592700022",
      image_url: "",
      text_summary: "Happy Holidays from us to you! We hope you're having a Sweet time with your loved ones! \nAre you spending the festive season playing Candy Crush? Tell us when a...",
      timestamp: "2019-12-24 14:00:00.000Z",
      tweet_id: "1209473805816479744",
      tweet_lng: "",
      user_id: "592700022",
      user_name: "CandyCrushSaga"
    },
    %TwitterFeed.Tweet{
      display_name: "Candy Crush Saga",
      handle_id: "592700022",
      image_url: "",
      text_summary: "Anybody else?! \n\nDo you spend the Holidays playing Candy Crush?https://twitter.com/JRxxxxxxx/status/1209361113487310848 …Don’t mind me \nI’ll just be playing can...",
      timestamp: "2019-12-24 09:47:13.000Z",
      tweet_id: "1209410188203216896",
      tweet_lng: "",
      user_id: "592700022",
      user_name: "CandyCrushSaga"
    },
    %TwitterFeed.Tweet{
      display_name: "App Store",
      handle_id: "592700022",
      image_url: "",
      text_summary: "Winter offer! Get extra-special boosters that last twice as long in @CandyCrushSaga\n\nView the deal in-game: http://apple.co/CandyCrushSaga pic.twitter.com/l47U1...",
      timestamp: "2019-12-23 13:20:13.000Z",
      tweet_id: "1209101406126383104",
      tweet_lng: "",
      user_id: "74594552",
      user_name: "AppStore"
    },
    %TwitterFeed.Tweet{
      display_name: "Candy Crush Saga",
      handle_id: "592700022",
      image_url: "https://pbs.twimg.com/media/EMgjh7jXUAUon4_.jpg",
      text_summary: "Earn points and get Sweet rewards this Holiday season! You can get x4 Google #PlayPoints on everything you buy in the Year End Deals in #CandyCrush before Decem...",
      timestamp: "2019-12-23 23:32:48.000Z",
      tweet_id: "1209255568038400002",
      tweet_lng: "",
      user_id: "592700022",
      user_name: "CandyCrushSaga"
    },
    %TwitterFeed.Tweet{
      display_name: "Candy Crush Saga",
      handle_id: "592700022",
      image_url: "https://pbs.twimg.com/media/EMe37k9WkAAb6tI.jpg",
      text_summary: "What a nice surprise!! It's Tiffi! Have you added all the new character costumes to your collection yet?! pic.twitter.com/XWbzNytJTX",
      timestamp: "2019-12-23 15:42:43.000Z",
      tweet_id: "1209137264145653769",
      tweet_lng: "",
      user_id: "592700022",
      user_name: "CandyCrushSaga"
    },
    %TwitterFeed.Tweet{
      display_name: "Candy Crush Saga",
      handle_id: "592700022",
      image_url: "https://pbs.twimg.com/media/EMOZdAHWwAAgL5b.jpg",
      text_summary: "Treat yourself to a crossword this weekend...Candy Crush style #CrossWordPuzzleDaypic.twitter.com/egvJL5EkS1",
      timestamp: "2019-12-21 14:00:00.000Z",
      tweet_id: "1208386641011904512",
      tweet_lng: "",
      user_id: "592700022",
      user_name: "CandyCrushSaga"
    },
    %TwitterFeed.Tweet{
      display_name: "Candy Crush Saga",
      handle_id: "592700022",
      image_url: "",
      text_summary: "What could be more Sweet than a home-made card? \n\nFollow the easy steps below to learn how to make a Candy Crush card for your loved ones and be sure to tag us ...",
      timestamp: "2019-12-20 16:02:07.000Z",
      tweet_id: "1208054984597819399",
      tweet_lng: "",
      user_id: "592700022",
      user_name: "CandyCrushSaga"
    },
    %TwitterFeed.Tweet{
      display_name: "Candy Crush Saga",
      handle_id: "592700022",
      image_url: "",
      text_summary: "Holidays are just around the corner but, wait! You can still make that Delicious Gift for your special one! \n\nGet them a Google Gift Card before New Year’s Eve ...",
      timestamp: "2019-12-19 17:46:08.000Z",
      tweet_id: "1207718774943694848",
      tweet_lng: "",
      user_id: "592700022",
      user_name: "CandyCrushSaga"
    },
    %TwitterFeed.Tweet{
      display_name: "Candy Crush Saga",
      handle_id: "592700022",
      image_url: "",
      text_summary: "Has anyone else got to level 6000 yet?! \n\nWe want to know!https://twitter.com/cesar_ogata/status/1207514179315392512 …Level 6000 !! pic.twitter.com/1EHvB5G8xO",
      timestamp: "2019-12-19 11:02:38.000Z",
      tweet_id: "1207617229082288128",
      tweet_lng: "",
      user_id: "592700022",
      user_name: "CandyCrushSaga"
    },
    %TwitterFeed.Tweet{
      display_name: "Candy Crush Saga",
      handle_id: "592700022",
      image_url: "",
      text_summary: "Today we're celebrating more than just new levels; today we hit level 6000! \nShare below if you've reached that milestone yet! pic.twitter.com/HxSj0GpUpk",
      timestamp: "2019-12-18 15:00:01.000Z",
      tweet_id: "1207314578675126272",
      tweet_lng: "",
      user_id: "592700022",
      user_name: "CandyCrushSaga"
    },
    %TwitterFeed.Tweet{
      display_name: "Candy Crush Saga",
      handle_id: "592700022",
      image_url: "",
      text_summary: "Reasons to play Candy Crush: \n\nWhat are yours? Let us know below! https://twitter.com/theroyalfinesse/status/1207233349686906880 …1. My Go to Stress Reliever.\n2...",
      timestamp: "2019-12-18 09:49:38.000Z",
      tweet_id: "1207236471482175488",
      tweet_lng: "",
      user_id: "592700022",
      user_name: "CandyCrushSaga"
    },
    %TwitterFeed.Tweet{
      display_name: "Candy Crush Saga",
      handle_id: "592700022",
      image_url: "",
      text_summary: "Who can give us THREE reasons why Candy Crush was the most important app you used this year?! https://twitter.com/AppStore/status/1206621005076189185 …What’s th...",
      timestamp: "2019-12-17 10:42:51.000Z",
      tweet_id: "1206887475651776512",
      tweet_lng: "",
      user_id: "592700022",
      user_name: "CandyCrushSaga"
    },
    %TwitterFeed.Tweet{
      display_name: "Candy Crush Saga",
      handle_id: "592700022",
      image_url: "",
      text_summary: "Us too https://twitter.com/JEUmogbai/status/1206841388467404800 …Curious to find out: \"what level are you on in the @CandyCrushSaga",
      timestamp: "2019-12-17 10:07:50.000Z",
      tweet_id: "1206878661388967937",
      tweet_lng: "",
      user_id: "592700022",
      user_name: "CandyCrushSaga"
    },
    %TwitterFeed.Tweet{
      display_name: "Candy Crush Saga",
      handle_id: "592700022",
      image_url: "",
      text_summary: "That's the #MondayMotivation we need today!! \n\nHow do you feel when you pass a level?! https://twitter.com/hessaa_alk/status/1206524514324099077 …i won a very h...",
      timestamp: "2019-12-16 11:51:28.000Z",
      tweet_id: "1206542353756033025",
      tweet_lng: "",
      user_id: "592700022",
      user_name: "CandyCrushSaga"
    },
    %TwitterFeed.Tweet{
      display_name: "Candy Crush Saga",
      handle_id: "592700022",
      image_url: "https://pbs.twimg.com/media/ELqGjMzWsAAjDzC.jpg",
      text_summary: "We're spilling the tea; Candy Crush goes nicely with a cup of your favorite hot drink ! \nWhat's your favorite flavour of tea?! \n\nThanks @nikki_starr_ for sharin...",
      timestamp: "2019-12-15 14:00:00.000Z",
      tweet_id: "1206212313583366144",
      tweet_lng: "",
      user_id: "592700022",
      user_name: "CandyCrushSaga"
    },
    %TwitterFeed.Tweet{
      display_name: "Candy Crush Saga",
      handle_id: "592700022",
      image_url: "",
      text_summary: "Follow the steps in the video to create your very own Color-Bomb decoration! \nA festive DIY delight! Make your very own Color-Bomb Bauble to decorate your tree ...",
      timestamp: "2019-12-13 14:00:00.000Z",
      tweet_id: "1205487536623280128",
      tweet_lng: "",
      user_id: "592700022",
      user_name: "CandyCrushSaga"
    },
    %TwitterFeed.Tweet{
      display_name: "Candy Crush Saga",
      handle_id: "592700022",
      image_url: "",
      text_summary: "Couldn't agree more https://twitter.com/MKarelyn/status/1205348068864004100 …If you really love someone you show it by sending them unsolicited candy crush live...",
      timestamp: "2019-12-13 10:47:22.000Z",
      tweet_id: "1205439061135167489",
      tweet_lng: "",
      user_id: "592700022",
      user_name: "CandyCrushSaga"
    },
    %TwitterFeed.Tweet{
      display_name: "Candy Crush Saga",
      handle_id: "592700022",
      image_url: "",
      text_summary: "The new @JumanjiMovie has brought Candy Crush to The Next Level! Play the exclusive in-game event with @TheRock and @KevinHart4Real for three-days only! Be sure...",
      timestamp: "2019-12-10 19:01:13.000Z",
      tweet_id: "1204476179719294976",
      tweet_lng: "",
      user_id: "592700022",
      user_name: "CandyCrushSaga"
    }
  ]
}

Function which is doing all this is

  def save_to_spreadsheet(handle, start_after_tweet \\ 0) do
    {:ok, pid} = GSS.Spreadsheet.Supervisor.spreadsheet("1Xs", list_name: "candy")

    fetch_tweets =
      TwitterFeed.get_tweets(handle, start_after_tweet)

    save_to_candy =
    fetch_tweets.tweets
      |> Enum.with_index(1)
      |> Enum.each(fn {tweet, index} ->
        GSS.Spreadsheet.append_rows(pid, index,
          [ tweet.handle_id,
            tweet.tweet_id,
            tweet.user_id,
            tweet.tweet_lng,
            tweet.user_name,
            tweet.display_name,
            tweet.timestamp,
            tweet.text_summary,
            tweet.image_url,
          ]
         )
    end)

    {:ok, save_to_candy}
  end

I've manually converted integer to strings but I'm still facing the same error.

[Copser]

@Voronchuk nevermind, I have just used append_row instead and it's working like a charm, thanks for this awesome library and your help.