Skip to content

Instantly share code, notes, and snippets.

@stanasiukcom

stanasiukcom/play-store_apps-script.gs

Created August 1, 2022 12:03
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save stanasiukcom/de79bad43d40ced961f06a0be9fddbd1 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save stanasiukcom/de79bad43d40ced961f06a0be9fddbd1 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
play-store_apps-script.gs
function runQuery() {
// Replace this value with the project ID listed in the Google
// Cloud Platform project.
const projectId = '';
const request = {
query: 'WITH lastSunday AS (SELECT CASE WHEN EXTRACT(DAYOFWEEK FROM CURRENT_DATE()) = 1 THEN DATE_SUB(CURRENT_DATE(),INTERVAL 3 DAY) ELSE DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) END AS d)' +
'SELECT Date, Package_Name, Product_ID, Country, New_Subscriptions, Cancelled_Subscriptions, Active_Subscriptions, _LATEST_DATE, _DATA_DATE FROM `` WHERE Date BETWEEN DATE_TRUNC((SELECT d FROM lastSunday), WEEK(MONDAY)) AND DATE_TRUNC(CURRENT_DATE(), WEEK(SUNDAY)) ORDER BY Date DESC',
useLegacySql: false
};
let queryResults = BigQuery.Jobs.query(request, projectId);
const jobId = queryResults.jobReference.jobId;
// Check on status of the Query Job.
let sleepTimeMs = 500;
while (!queryResults.jobComplete) {
Utilities.sleep(sleepTimeMs);
sleepTimeMs *= 2;
queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
}
// Get all the rows of results.
let rows = queryResults.rows;
while (queryResults.pageToken) {
queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {
pageToken: queryResults.pageToken
});
rows = rows.concat(queryResults.rows);
}
if (!rows) {
Logger.log('No rows returned.');
return;
}
const subSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Play Store Subscriptions');
SpreadsheetApp.setActiveSheet(subSheet)
const sheet = SpreadsheetApp.getActiveSheet();
// Don't append the headers.
const headers = queryResults.schema.fields.map(function(field) {
return field.name;
});
// sheet.appendRow(headers);
// Append the results.
var data = new Array(rows.length);
for (let i = 0; i < rows.length; i++) {
const cols = rows[i].f;
data[i] = new Array(cols.length);
for (let j = 0; j < cols.length; j++) {
data[i][j] = cols[j].v;
}
}
var lastRow = sheet.getLastRow();
var Avals = sheet.getRange("A1:A"+lastRow).getValues();
var Alast = lastRow - Avals.reverse().findIndex(c=>c[0]!='');
sheet.getRange(Alast + 1, 1, rows.length, headers.length).setValues(data);
}
function runPaymentsQuery() {
// Replace this value with the project ID listed in the Google
// Cloud Platform project.
const projectId = '';
const request = {
// TODO (developer) - Replace query with yours
query: 'WITH lastSunday AS (SELECT CASE WHEN EXTRACT(DAYOFWEEK FROM CURRENT_DATE()) = 1 THEN DATE_SUB(CURRENT_DATE(),INTERVAL 3 DAY) ELSE DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) END AS d)' +
'SELECT Order_Number, Order_Charged_Date, Order_Charged_Timestamp, Financial_Status, Device_Model, Product_Title, Product_ID, Product_Type, SKU_ID, Currency_of_Sale, Item_Price, Taxes_Collected, Charged_Amount, City_of_Buyer, State_of_Buyer, Postal_Code_of_Buyer, Country_of_Buyer, Base_Plan_ID, Offer_ID, _LATEST_DATE, _DATA_DATE FROM `` WHERE Order_Charged_Date BETWEEN DATE_TRUNC((SELECT d FROM lastSunday), WEEK(MONDAY)) AND DATE_TRUNC(CURRENT_DATE(), WEEK(SUNDAY)) ORDER BY Order_Charged_Date DESC',
useLegacySql: false
};
let queryResults = BigQuery.Jobs.query(request, projectId);
const jobId = queryResults.jobReference.jobId;
// Check on status of the Query Job.
let sleepTimeMs = 500;
while (!queryResults.jobComplete) {
Utilities.sleep(sleepTimeMs);
sleepTimeMs *= 2;
rows = rows.concat(queryResults.rows);
}
// Get all the rows of results.
let rows = queryResults.rows;
while (queryResults.pageToken) {
queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {
pageToken: queryResults.pageToken
});
rows = rows.concat(queryResults.rows);
}
if (!rows) {
Logger.log('No rows returned.');
return;
}
const subSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Play Store Payments');
SpreadsheetApp.setActiveSheet(subSheet)
const sheet = SpreadsheetApp.getActiveSheet();
// Don't append the headers.
const headers = queryResults.schema.fields.map(function(field) {
return field.name;
});
// sheet.appendRow(headers);
// Append the results.
var data = new Array(rows.length);
for (let i = 0; i < rows.length; i++) {
const cols = rows[i].f;
data[i] = new Array(cols.length);
for (let j = 0; j < cols.length; j++) {
data[i][j] = cols[j].v;
}
}
var lastRow = sheet.getLastRow();
var Avals = sheet.getRange("A1:A"+lastRow).getValues();
var Alast = lastRow - Avals.reverse().findIndex(c=>c[0]!='');
sheet.getRange(Alast + 1, 1, rows.length, headers.length).setValues(data);
}
function runInstallsQuery() {
// Replace this value with the project ID listed in the Google
// Cloud Platform project.
const projectId = '';
const request = {
// TODO (developer) - Replace query with yours
query: 'WITH lastSunday AS (SELECT CASE WHEN EXTRACT(DAYOFWEEK FROM CURRENT_DATE()) = 1 THEN DATE_SUB(CURRENT_DATE(),INTERVAL 3 DAY) ELSE DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) END AS d)' +
'SELECT Date, Package_Name, SUM(Daily_Device_Installs) AS Daily_Device_Installs, SUM(Daily_Device_Uninstalls) AS Daily_Device_Uninstalls, SUM(Daily_Device_Upgrades) AS Daily_Device_Upgrades, SUM(Total_User_Installs) AS Total_User_Installs, SUM(Daily_User_Installs) AS Daily_User_Installs, SUM(Daily_User_Uninstalls) AS Daily_User_Uninstalls, SUM(Active_Device_Installs) AS Active_Device_Installs, SUM(Install_events) AS Install_events, SUM(Update_events) AS Update_events, SUM(Uninstall_events) AS Uninstall_events FROM `` WHERE Date BETWEEN DATE_TRUNC((SELECT d FROM lastSunday), WEEK(MONDAY)) AND DATE_TRUNC(CURRENT_DATE(), WEEK(SUNDAY)) GROUP BY 1, 2 ORDER BY Date DESC',
useLegacySql: false
};
let queryResults = BigQuery.Jobs.query(request, projectId);
const jobId = queryResults.jobReference.jobId;
// Check on status of the Query Job.
let sleepTimeMs = 500;
while (!queryResults.jobComplete) {
Utilities.sleep(sleepTimeMs);
sleepTimeMs *= 2;
queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
}
// Get all the rows of results.
let rows = queryResults.rows;
while (queryResults.pageToken) {
queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {
pageToken: queryResults.pageToken
});
rows = rows.concat(queryResults.rows);
}
if (!rows) {
Logger.log('No rows returned.');
return;
}
const subSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Play Store Installations');
SpreadsheetApp.setActiveSheet(subSheet)
const sheet = SpreadsheetApp.getActiveSheet();
// Don't append the headers.
const headers = queryResults.schema.fields.map(function(field) {
return field.name;
});
// sheet.appendRow(headers);
// Append the results.
var data = new Array(rows.length);
for (let i = 0; i < rows.length; i++) {
const cols = rows[i].f;
data[i] = new Array(cols.length);
for (let j = 0; j < cols.length; j++) {
data[i][j] = cols[j].v;
}
}
var lastRow = sheet.getLastRow();
var Avals = sheet.getRange("A1:A"+lastRow).getValues();
var Alast = lastRow - Avals.reverse().findIndex(c=>c[0]!='');
sheet.getRange(Alast + 1, 1, rows.length, headers.length).setValues(data);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment