Saving data in Google Sheets with Google Apps Script

In a previous post, I looked at importing social media data into a Google Sheet. Since then, I’ve had a few readers contact me to ask if there’s a way to save the imported data, so that you have a record of the data at set time intervals (e.g. once a day or once a week).

The answer is: Absolutely!

It involves writing a short script but it’s pretty simple and we can even set up the whole shebang to run automatically in the background.

save data in google sheet

Creating a Save Data function with Google Apps Script

Ok, so here’s the scenario: I’m using Google Sheet’s IMPORT() formulas to get hold of follower counts for a social media channel (in this case, the number of followers of the Reddit Space group). This is running in cells A1 and B1. The formula to get the Reddit followers is:


For more details, see my previous post on importing various different social media channel metrics into Google Sheets.

First, let’s add a custom menu to Google Sheets which saves the current number of followers plus a timestamp in the rows below.

Second, we’ll look at setting up an automatic trigger to do this for us at set intervals (e.g. once per day).

The steps in detail then:

  1. Assume you have data in A1 and B1, put the formula =now() into cell C1, to give us our timestamp. Our spreadsheet should look like this:
  2. Import social media followers into Google Sheet

  3. Type headings into row 4, as shown in this image:
  4. headings

  5. Next go to Tools > Script editor…
  6. This opens a new tab in your browser. Remove the existing code in the main window (the bit of code: function myFunction() { }).
  7. Paste the following code into the window:
  8. This adds two functions to our spreadsheet, one to save the data and the other to create a new custom menu so we can run our save data function from within our spreadsheet.

  9. Hit save.
  10. Return to your spreadsheet window.
  11. Reload your spreadsheet (refresh your browser) and you should see a new menu: Custom Menu > Save Data item
  12. Run this. First time it’ll ask you for permission so click Allow.
  13. Google Sheet App Script Permission

  14. When it runs, it will append a new row beneath your existing entries with a copy of the data from row 1.
  15. You can now re-run this whenever you want and it’ll save a copy of the current values and timestamp below.

saved data in Google sheets

Cool huh?!?

But what if we forget to do it one day, or we’re inconsistent with our time periods. It’ll result in patchy data.

It’d be much better if we could automate it to run at set intervals.

Thankfully we can, and it’s super easy with Google Sheets.

Automatic Data Saving

Steps for automatic data saving:

  1. Go to back to your script editor (Tools > Script editor… if you closed that tab).
  2. Click on Resources > Current project’s triggers
  3. This brings up the triggers pane. In this pane, click on No triggers set up. Click here to add one now.
  4. In the first drop down, select the Save Data function, as shown in this image:
  5. Function trigger in Google Apps Script

  6. For the remaining drop down options, select Time Driven and then the period you’re interested in.
  7. Voila! Come back after a while and you should have historic data saved in your spreadsheet (you may need to reload the sheet by refreshing your browser). It really is that easy.

Any comments or questions, feel free to leave them in the comments below!

5 thoughts on “Saving data in Google Sheets with Google Apps Script”

  1. Hi, Ben!!
    First that all, thank you for everything, this is so amazing!
    I want to ask you about Twitter code crash often, do you have any idea how to solve it, or why is happening? Both of them are having problems.
    It works normally but after a few minutes shows #VALUE message.

    Best regards,

    1. Thanks Luisa! Glad to be of help.

      The IMPORT functions can be temperamental and are affected by things like caching and changes to the XML/HTML structure of the original URL. Try adding (or removing) a final “/” at the end of the URL as Google will treat this as a new, different URL to go and fetch, so the caching is not an issue. I’ve found this sometimes fixes the issue.

      Also, in your function you could wrap the import XML function in an IFERROR function to have a custom error messages instead of a #N/A. For example, my function would look like this (assuming the Twitter URL is in cell A1):

      =iferror(query(IMPORTXML(A1,"//a[@data-nav='followers']"),"select Col2"),"Not available at this time")

      In my other web scraping article, I list two Twitter import methods so you try both, and then pick the one that works using an IF function for example.

      1. Ben,

        Thank you again. I add a “/” at the end of the URL, and it works. I will let you know if crash it again. You’re awesome, I am so into this (code in Google SpredSheets) after I read your blogs.
        Looking for learning more, do you know some free resources that I can look?


        1. Great!

          I find these sites pretty useful for Google Sheets work:
          Official Google Sheets documentation
          Google Sheets forums

          For more developer orientated work inside Google sheets using App Script, check out these ones:
          Introduction to Apps Scripts
          App Script documentation
          Digital Inspiration – often has Google Sheets articles e.g.

          Also, keep an eye out here, lots more content on Google Sheets coming this way! I’m creating a Google Sheets dashboard course and hoping to launch in a couple of months.

          1. Thanks,

            Let me know if you need help. I will work for free to learn more about this project. I will take a look and make it my goal for this year.

            Thank you for the inspiration and the good content.

Leave a Reply

Your email address will not be published. Required fields are marked *