Import data to google sheets

186 views
Skip to first unread message

Lily Nicole Talmers

unread,
May 1, 2021, 11:45:16 AM5/1/21
to Google Apps Script Community
I want to import data from formsubmit.co to Google Sheets. I have searched on Google and YouTube but no info.

Any help will be appreciated.

Alan Wells

unread,
May 1, 2021, 1:11:25 PM5/1/21
to Google Apps Script Community
I haven't tested this code.
So, I'm not sure if it has any bugs or not.
But it should be complete enough to explain what needs to be done.
If you have any questions, post back.

First enter your email address for FormSumbit into the getAPI_key function var myEmail
Run the function getAPI_key.
Hard code the API key into the function writeDataToSheet.
Hard code the spreadsheet file ID and sheet tab name into the function writeDataToSheet
Run writeDataToSheet

function getAPI_key() {
  var ao,data,json,myEmail,response;
  
  /*
    First get your API key which is needed to make a request to get your data
  
  */
  
  myEmail = "Enter the email you use for Form Submit here";
  ao = {};
  
  ao.url = "https://formsubmit.co/api/get-apikey/" + myEmail;
  
  response = makeRequest(ao);
  
  data = response.getContentText();
  Logger.log('data: ' + data)
  
  json = JSON.parse(data);
  Logger.log('json: ' + json)
}

function writeDataToSheet() {
  var answers,apikey,data,i,innerArr,k,L,sh,ss,submissions,thisAnswer,values;
  
  apikey = "Enter API key here";

  data = getFormSubmitData(apikey);

  if (data.success !== 'true') {
    Logger.log('ERROR')
    return;
  }
  
  submissions = data.submissions;//An array of objects
  
  L = submissions.length;
  
  values = [];
  
  for (i=0;i<L;i++) {
    innerArr = [];
    answers = submissions[i].form_data;//Get an object of answers out of the object
    
    for (k in answers) {
      thisAnswer = answers[k];
      innerArr.push(thisAnswer);
    }
    values.push(innerArr)
  }
  
  ss = SpreadsheetApp.openById("Put the spreadsheet file ID here");
  sh = ss.getSheetByName("Put the name of the sheet tab here");
  
  sh.getRange(1, 1, values.length, values[0].length).setValues(values);//Set all values at once
}

function getFormSubmitData(API_Key) {
  var ao,data,json,response;

  ao = {};
  
  
  response = makeRequest(ao);
  
  data = response.getContentText();
  Logger.log('data: ' + data)
  
  json = JSON.parse(data);
  Logger.log('json: ' + json)
  
  return data;
}

function makeRequest(po) {
try{
  var i,L,r;
  /*
    po.url - the url to make the https request to
    po.options - the payload and method and any other options - Will default to GET
    po.loopCnt - number of times to try
    po.logError - log an error if there is a failure - false - dont log error
  */
  
  /* Because payload is a JavaScript object it will be interpreted as
   as form data (No need to specify contentType- it will automatically
   default to either 'application/x-www-form-urlencoded'
   or 'multipart/form-data')  */
  
  //Logger.log('po: ' + po)
  
  if (!po.url) {
    console.error('Error Code - 98765 - No URL passed in. Parameters:' + JSON.stringify(po));
    return;
  }
  
  if (!po.options) {//Always need options because always need to mute exceptions
    po.options = {};
  }
  
  L = po.loopCnt ? po.loopCnt : 3;
  
  po.options.muteHttpExceptions = true;//Make sure this is always set
  
  for (i=1;i<L;i++) {
    try{
      r = UrlFetchApp.fetch(po.url, po.options);
      break;
    }catch(e){
      if (i!==L-1){Utilities.sleep(i*2000);}
      if (i>=L-1) {
        if (!po.logError) {
          break;
        }
        Logger.log('error 53: '  + e.error)
        console.error('ERROR - qhyyfhvxud6tr46f8g - : ' + e + ' Stack: ' + e.stack + "\n\n" + r);
      }
    };
  }
  
  //Logger.log('r 59: ' + r)
  
  if (!r) {
    console.error('ERROR - getting response' );
    return;
  }
  
  //Logger.log('r.getResponseCode() 66: ' + r.getResponseCode());

  if (r && r.getResponseCode() !== 200) {//There should always be a response unless the
    //Fetch call failed and if that happens then the outer try - catch can handle it
    //Logger.log('code is not ' + ' 200')
    if (r && r.getResponseCode() !== 200) {//There should always be a response unless the
      //Fetch call failed and if that happens then the outer try - catch can handle it
      //Logger.log('code is not' + '200')
      var arg = po ? JSON.stringify(po) :"po not passed in";
      Logger.log('ERROR ' + r + '\npo:' + arg);
      return false;
    }
    return false;
  }
  
  //Logger.log('r 81: ' + r)
  return r;
}catch(e){
  var rzpnzCode,theTxt;
  
  if (r) {
    try{
      rzpnzCode = r.getResponseCode();
    }catch(e) {
      Logger.log('ERROR: ' + e.message + "\n\nStack: " + e.stack);
    }
  }
  
  if (rzpnzCode !== 200) {//There could be an error even if the Request was successful - but if the request
    //was successful an error message should not be sent out because the error could just be that the receiving
    //URL did not return anything - which could happen a lot even when everything is fine
    try {
      theTxt = r.getContentText();
    } catch(e) {
      theTxt = 'Error getting information out of the return value';
    }
    
    Logger.log('ERROR: ' + e.message + "\n\nStack: " + e.stack + "\nThe message: " + theTxt);
  }
  return r;
};

Lily Nicole Talmers

unread,
May 1, 2021, 4:23:16 PM5/1/21
to Google Apps Script Community

At first I opened Google Sheets>Tools>Script Editor. Where I wrote this script:

function getAPI_key() {
  var ao,data,json,myEmail,response;
  
  /*
    First get your API key which is needed to make a request to get your data
  
  */
  
  myEmail = "my.e...@gmail.com";
  ao = {};
  
  ao.url = "https://formsubmit.co/api/get-apikey/" + myEmail;
  
  response = makeRequest(ao);
  
  data = response.getContentText();
  Logger.log('data: ' + data)
  
  json = JSON.parse(data);
  Logger.log('json: ' + json)
}

Saved it and hit Run. But it is giving me this error:
ReferenceError: makeRequest is not defined
getAPI_key @ Code.gs:14

Thanks a lot for the help AJ.

Alan Wells

unread,
May 1, 2021, 4:31:14 PM5/1/21
to Google Apps Script Community
There are 4 functions in the code that I posted.
One of the functions is named:
makeRequest

You need to paste in all 4 of the functions that I provided.
Since the code is bound to a Sheet, then you can change the line:
ss = SpreadsheetApp.openById("Put the spreadsheet file ID here");
TO:
ss = SpreadsheetApp.getActiveSpreadsheet();

Lily Nicole Talmers

unread,
May 1, 2021, 5:29:48 PM5/1/21
to Google Apps Script Community
function writeDataToSheet() {
  var answers,apikey,data,i,innerArr,k,L,sh,ss,submissions,thisAnswer,values;
  
  apikey = "myapikey12345";

  data = getFormSubmitData(apikey);

  if (data.success !== 'true') {
    Logger.log('ERROR')
    return;
  }
  
  submissions = data.submissions;//An array of objects
  
  L = submissions.length;
  
  values = [];
  
  for (i=0;i<L;i++) {
    innerArr = [];
    answers = submissions[i].form_data;//Get an object of answers out of the object
    
    for (k in answers) {
      thisAnswer = answers[k];
      innerArr.push(thisAnswer);
    }
    values.push(innerArr)
  }
  
  ss = SpreadsheetApp.openById("edited1GC6L76A4vSKvR32iqSQqDo");
  sh = ss.getSheetByName("Sheet1");

Using your suggestion I was able to get the API key. And now I am using this to get the values. But it is showing me this in the execution log:

Logging output too large. Truncating output. data: {
    "success": true,
    "submissions": [
        {
            "form_url": "http://localhost:3000/",
            "form_data": {
                "name": "From a...@a.com",
                "message": "{\n  \u0022beaf\u0022: \u002210\u0022,\n  \u0022cheese\u0022: \u002210\u0022,\n  \u0022bread\u0022: \u002210\u0022,\n  \u0022country\u0022: \u0022AA\u0022,\n  \u0022city\u0022: \u0022AA\u0022,\n  \u0022zipcode\u0022: \u0022AA\u0022,\n  \u0022email\u0022: \u0022a...@a.com\u0022,\n  \u0022totalPrice\u0022: 30,\n  \u0022myurl\u0022: \u0022https://google.com/\u0022\n}"
            },
            "submitted_at": {
                "date": "2021-04-30 14:35:22.000000",
                "timezone_type": 3,
                "timezone": "UTC"
            }
        },

But there is nothing showing up on my Google Sheets

Alan Wells

unread,
May 1, 2021, 5:57:36 PM5/1/21
to Google Apps Script Community
Okay, well, the request for data was successful and returned your submission data.
You can see:
"success": true,
In the object at the top.
So, comment out most of the Logger.log( 
lines because of that error.
Especially comment out the Logger.log lines
that log the data.

Add another Logger.log() line after:
L = submissions.length;
Logger.log("L: " + L)

Run the code and see if you get a value for L which is the number of submissions in your data.

Reply all
Reply to author
Forward
0 new messages