Load data into Google Spreadsheet from PHP

Lets say you have a PHP script that returns a value, based on a query string:

mysite.com/query.php?string=blah

Getting the result of that PHP script into Google Sheets is rather easy. Open Tools > Script Editor in Google Sheets and create a function like this:

function getMyQueryValue(value) {
   var response = UrlFetchApp.fetch("https://mysite.com.com/query.php?string=" + value);
   Logger.log(response.getContentText());
   return JSON.parse(response);
}

Now in Google Sheets simply type =getMyQueryValue(A1) and it will pass the value of A1 over to your PHP script and return the result into your Google Sheet!


There’s also a really cool 1 liner way to inject data into Google Sheet if the remote host returns XML/HTML.. Lets pretend get-xml.php?query=2020-01-01 returns this content:

<result>
  <value>12</value>
</result>

Then in Google Sheets you simply have to use the built in importxml function like this:

=VALUE(REGEXREPLACE(IMPORTXML("https://mysite.com/get-xml.php?query=" & A1 , "//value"),"\D+", ""))

Read more about IMPORTXML here.

Leave a Reply

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